Test Data Type of String returned by InputBox

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
L

Legacy 98055

Guest
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530

ADVERTISEMENT

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

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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
 
L

Legacy 98055

Guest
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

Well-known Member
Joined
Aug 4, 2002
Messages
851
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top