Textbox text Validate

[Mas]

Hi,

I have textbox1 in userform1

and in my sheet 1

column A column B column C
01 01 0211
02 02 0251
.... .. ...
up to 11 up to 60 up to 0911
(text) (text) (text)

some items are missing (not in serial in all the tree
columns

Is it possible that in my textbox1 when i enter data it
will be a combination of a+b+c ?
the first to digit should be from column A and next two
digit from column B and the last four digit from column c?

thanks in advance

MAS

 

[Bob Phillips]

Mas,

Are you asking for some code that will allow those values to be input, or
check whether the values entered will already exist in A, B, C. And should
it always be the full number of digits?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mas]

it should always be the full number of digits.

i am looking for some code that will check whther the
number entered in the text box is a combination of a, b
and c. ie first two digit from A second two digits from B
and the last four digits from C
if i input something starting with 12 which is not in
column A it should not accept, likewise if the first two
digit is 11 (which is in column A) and next two digit is
61 (which is not in clumn b) it should not accept)

if i merge column a, b and c i am afraid the combination
of digits will be a big list.

Thanks

 

[Bob Phillips]

Hi Mas,

here is some code. I have tied it to a button as the trigger

Private Sub CommandButton1_Click()
With Me.TextBox1
If Len(.Text) <> 8 Or Not ValidInput Then
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
MsgBox "Invalid Input"
End If
End With
End Sub

Function ValidInput() As Boolean
Dim colA As String
Dim colB As String
Dim colC As String
Dim oFound As Range

With Me.TextBox1
colA = Mid(.Text, 1, 2)
colB = Mid(.Text, 3, 2)
colC = Mid(.Text, 5, 4)
Set oFound = Worksheets("Sheet1").Columns("A:A").Find(CLng(colA))
If oFound Is Nothing Then
ValidInput = False
Exit Function
End If
Set oFound = Worksheets("Sheet1").Columns("B:B").Find(CLng(colB))
If oFound Is Nothing Then
ValidInput = False
Exit Function
End If
Set oFound = Worksheets("Sheet1").Columns("C:C").Find(CLng(colC))
If oFound Is Nothing Then
ValidInput = False
Exit Function
End If
ValidInput = True
End With
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)