Checking Input box

  • Thread starter Thread starter AMK4
  • Start date Start date
Hi AMK4,
try this.
I've changed to variant and put the code lines that wouldn't work with
string data back in since they do work with variant.
If you just want the InputBox to remain in place until the user enters
a 1,2,3 or 4 rather than show the invalid entry message just delete
line that goes...

If Not ValidPage Then MsgBox "Only values between 1 and 4 are
allowed.", 16

Also, I found two new problems (don't panic, they're solved, I hope!).

1. What if the user enters a decimal eg 1.2 I've included a test for
integer value, namely
Or Int(myPageInput) <> CSng(myPageInput) Then

2. What if the user enters a string other than true. This results in
the Type Mismatch error so I've had to resort to "On Error Resume Next"
which forces Excel to ignore the error. The code then goes on to let
ValidPage = False so the loop is not exited. After that line the "On
Error Goto 0" reactivates Excels error detection ability.

Sub copyData()
Dim myPageInput 'was String
Dim ValidPage As Boolean
Do While Not ValidPage
myPageInput = Application.InputBox(prompt:="Which form should this go
on? (1 through 4)", _
Title:="Form number", Type:=2)
'Your 2nd AND in next line made no difference so I took it out
If Application.IsLogical(myPageInput) And myPageInput <> True Then Exit
Sub
ValidPage = True
'with variant myPageInput next three lines work
On Error Resume Next
If myPageInput < 1 _
Or myPageInput > 4 _
Or Int(myPageInput) <> CSng(myPageInput) Then 'back in + test for
integer
On Error GoTo 0
ValidPage = False 'back in
End If 'back in
If Not ValidPage Then MsgBox "Only values between 1 and 4 are
allowed.", 16 'back in
Loop

MsgBox "myPageInput: " & myPageInput, 64
End Sub

I'll now be spending a little time undoing all the knots that have
formed in my brain;-)

Ken Johnson
 
Hi AMK4,
Just in case the line breaks are a problem (which they probably aren't
for you) here's the code in direct pastable form (I hope, it's always a
gamble)

Sub copyData()
Dim myPageInput 'was String
Dim ValidPage As Boolean
Do While Not ValidPage
myPageInput = Application.InputBox( _
prompt:="Which form should this go on? (1 through 4)", _
Title:="Form number", Type:=2)
'Your 2nd AND in next line made no difference so I took it out
If Application.IsLogical(myPageInput) And myPageInput <> True _
Then Exit Sub
ValidPage = True
'with variant myPageInput next three lines work
On Error Resume Next
If myPageInput < 1 _
Or myPageInput > 4 _
Or Int(myPageInput) <> CSng(myPageInput) Then 'back in
'+ test for integer
On Error GoTo 0
ValidPage = False 'back in
End If 'back in
If Not ValidPage Then _
MsgBox "Only values between 1 and 4 are allowed.", 16 'back in
Loop
MsgBox "myPageInput: " & myPageInput, 64
End Sub
Ken Johnson
 
Back
Top