VBA textbox.value contains a number?

[jkeyes]

I going on little sleep today and am finding it hard to figure this one out... I searched the forums but my parameters didn't return anything appropriate...

All I need to know is, in VBA, how do I check if a textbox's value has a number in it? I only want users to be able to enter one or two letters...

Thanks, I know this is simple, but like I said I can't think today

 

[Norie]

Really depends where the textbox is located and how you created it, but you probably want to look at IsNumber.

PS

enter one or two letters...

?Typo.

 

[jkeyes]

Thanks Norie, I'll look into that...

That was not a typo, BTW. The textbox is for the user to enter a column letter, which may be one or two letters. This is why I want to make sure they do not enter a number...

Of course, if you have any other suggestions for how I might gather that info on a userform, I'm all ears!

 

[Norie]

Ah, well I assumed you wanted to check that they did enter a number.

As to any other suggestions, can't really say because I don't really know what you are actually doing.

Perhaps a RefEdit control?

 

[Joe Was]

Try this as a working sample:


Sub myCode()
'Standard module code, like: Module1.
Dim strMyText$
Dim lngChar&, lngMyCnt&, lngMyTextLen&

strMyText = InputBox("Enter your two letter code below:", "Get Code!")
lngMyTextLen = Len(strMyText)

For lngChar = 1 To Len(strMyText)
Select Case Mid(strMyText, lngChar, 1)

Case "A" To "Z", "a" To "z"
lngMyCnt = lngMyCnt + 1
End Select
Next lngChar

If (lngMyCnt <> 2 Or lngMyTextLen <> lngMyCnt) Then
MsgBox "You must enter a ""two-letter"" code, only!", _
vbCritical + vbOKOnly, _
"Code InPut Error!"
Else

MsgBox "The ""Code"" you entered is:" & Space(3) & strMyText, _
vbInformation + vbOKOnly, _
"Code InPuted!"
End If
End Sub

 

[Joe Was]

If you would have indicated the whole correct problem, first, you would have had a better solution and not have wasted time!

Try this, it gets the Column Letter and Number based upon the letters inputed by the user:


Sub myColumn()
'Standard module code, like: Module1.
Dim strMyText$
Dim lngChar&, lngMyCnt&, lngMyTextLen&, lngMyCol&

On Error GoTo myErr
strMyText = InputBox("Enter the Column Letter ID, to work with below:", "Get Column!")

lngMyCol = Range(strMyText & ":" & strMyText).Column
lngMyTextLen = Len(strMyText)

For lngChar = 1 To Len(strMyText)
Select Case Mid(strMyText, lngChar, 1)

Case "A" To "Z", "a" To "z"
lngMyCnt = lngMyCnt + 1
End Select
Next lngChar

If lngMyCnt = 0 Then GoTo myErr
If lngMyCnt > 2 Then GoTo myErr
If lngMyTextLen <> lngMyCnt Then GoTo myErr

MsgBox "You entered Column:" & Space(3) & UCase(strMyText) & vbLf & _
Space(15) & "or" & vbLf & _
Space(7) & "Column Number:" & Space(3) & lngMyCol, _
vbInformation + vbOKOnly, _
"Column Selected!"
GoTo myEnd

myErr:
MsgBox "You must enter a ""One or Two letter"" Column code, only!", _
vbCritical + vbOKOnly, _
"Column InPut Error!"

myEnd:
End Sub


It will be easy to convert this to whatever you use to get this user info.