Test Data Type of String returned by InputBox

[BigC]

I have an Inputbox to seek input from the user, being the letters of any single hidden column that the user wishes to unhide.

However, I want to build in some error handlers to deal with "invalid input", viz; more than two letters & non-alpha characters (#,%,&, etc), numbers.

I have got the first test (> 2 chars) covered
I'm not too bothered about the second, but
I'm having trouble detecting the third (numbers) because InputBox returns a string - which by definition is text!!

How can I include the test:
If InputBoxString = numbers, then MsgBox = "Invalid input"?

(Alternatively, is there a test I can use to detect both tests 2 & 3 as "non-text")

I tried worksheet functions IsNontext & IsNumber - but they don't work because the string (even if it looks like a number) is still text.

Thanks in advance

 

[Legacy 98055]

Hi BigC.
Seeing that you are looking for a valid range, don't worry so much about the type of characters begin sent or the type of variable being returned. For example, even if you get two alpha characters, "IX" would raise an error further along in your code. It would follow your rules but would not be a valid column seeing that the last column is "IV". So test for a valid range instead.<pre>
Sub SubTestRange()
Dim rng As Range, InputBoxString As String
InputBoxString = InputBox("HI")

On Error Resume Next
' if not a valid range an error will be raised
Set rng = ActiveSheet.Columns(InputBoxString)
If Err.Number <> 0 Then
On Error GoTo 0
Err.Clear
'deal with your error here
End If
Set rng = Nothing
On Error GoTo 0

End Sub

</pre>
tom

 

[Mark O'Brien]

TsTom's posted, but I spent a little bit of time and came up with this exercise in programming:

<pre>
Option Explicit

Public Sub main()
Dim sText As String
sText = GetText
MsgBox sText
End Sub
Private Static Function GetText() As String
Dim sText As String

sText = InputBox("Enter text")

If Not IsText(sText) Then
MsgBox "Oi, letters only", vbInformation, "Moron Error"
GetText
End If
GetText = sText
End Function
Private Function IsText(ByVal sText As String) As Boolean
'Declare Variables
Dim i As Integer
Dim iAscCode As Integer
Dim sUpperText As String

'Convert to UpperCase to make validation easier
sUpperText = UCase(sText)

For i = 1 To Len(sUpperText)
iAscCode = Asc(Mid(sUpperText, i, 1))
If iAscCode < Asc("A") Or iAscCode > Asc("Z") Then
IsText = False
Exit Function
End If
Next

'If the code gets this far, the text is valid. i.e. uses only letters
IsText = True

End Function</pre>

Admittedly a userform with be so much better with this kind of validayion though.

HTH

 

[Legacy 98055]

"Moron Error"
Wait until Dreamboat see that!

 

[Mark O'Brien]

She probably will, now that you've highlighted it.

 

[BigC]

Thanks guys
I think Tom's solution will be easier to add into my existing code than Mark's (no offence intended) , so I'm going to try that first.

However, I'm a bit lost when it comes to On Error. Where do I put my code to do what I want it to do (ie. test to see if required column if not already unhidden, and unhide it if it's not)?

Tx

 

[Legacy 98055]

No need to test. If you want to unhide it, even if it is already unhidden, no harm is done.<pre>Sub SubTestRange()
Dim rng As Range, InputBoxString As String
PlayItAgainSam:
InputBoxString = InputBox("HI")

On Error Resume Next
' if not a valid range an error will be raised
Set rng = ActiveSheet.Columns(InputBoxString)
If Err.Number<> 0 Then
On Error GoTo 0
Err.Clear
MsgBox "According to Mark O'Brien, you must me a complete moron!" & _
"Please select a valid column heading. A to IV"
GoTo PlayItAgainSam
End If
'###############################
rng.EntireColumn.Hidden = False
'###############################
Set rng = Nothing
On Error GoTo 0

End Sub</pre>
tom
This message was edited by TsTom on 2002-11-14 02:31

 

[BigC]

Tom
Many thanks again - you're a champion.

Do I detect some friendly rivalry between you & Mark O'Brien??
(I value his (& your) future contributions so I haven't hard wired into my code the suggested rebuff to dumkoff users that you attributed to him. Thanks for the idea anyway!!)

Cheers