VBA user input error handling

[rharn]

I have a userform that requires the user to input an integer between the range of 0-8 for the sub to function correctly. My dilemma is right now I have specified an if/then statement that shows a message box informing the user if they are out of bounds, however after the user clicks ok on the msgbox, the sub skips to the end, and unloads my userform. Is there anyway to inform the user that they are out of range and return to the userform for them to correct their input value?

 

[rs2k]

Can you post your code?

 

[rharn]

The program itself is over 1000 lines long haha...I was hoping for a simple 1 liner that just tells my sub to return to the userform instead of executing the rest of its functions.

 

[Ruddles]

Yes but I'm assuming you would like us to use the same variable names in our sample of code as you're using.

Let's see just the code around the MsgBox statement?

 

[Trevor G]

What about checking if it is a number

something like:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Var1()<br><SPAN style="color:#00007F">Dim</SPAN> varInput <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>varInput = InputBox("Enter a number")<br><SPAN style="color:#00007F">If</SPAN> IsNumeric(varInput) <SPAN style="color:#00007F">Then</SPAN><br><SPAN style="color:#007F00">'do what you want</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br>MsgBox "Its not a number"<br><SPAN style="color:#007F00">'then go to the field on the form</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[rharn]

The basic gist of my code looks like the following:

    Dim TextBox1 As Long, TextBox2 As Long
 
    TextBox1 = ILsearch.TextBox1.Value
    TextBox2 = ILsearch.TextBox2.Value
 
If (TextBox1 And TextBox2 <= 8) And (TextBox1 And TextBox2 > 0) Then
'
'executes desired sub
'
Else: MsgBox ("Database only includes ionic liquids with a maximum of 8 carbons in cation")
 
End if

So when I enter a value greater than 8 my msgbox pops up as follows but after I click ok it continues on with the sub and unloads my userform and activates another worksheet. I can include more code if necessary also. Thanks.

 

[Ruddles]

What triggers this code? A command button?

 

[RoryA]

Could you not just:

Else
   MsgBox ("Database only includes ionic liquids with a maximum of 8 carbons in cation")
   Exit Sub
End if

 

[rharn]

My command button code looks like this

Private Sub CommandButton1_Click()
    PropertySearch.Search
    ActiveSheet.Name = "SearchResult"
    Cells(1, 1).Select
    Unload ILsearch
End Sub

And my purpose is to avoid exiting the sub, if they have values entered into the userform I want to preserve it but inform them that one of the values they have entered is unacceptable.

 

[RoryA]

Then you need to return something from the called routine to indicate that you should not unload. You could convert it to a function that returns True/False depending on whether it completes and then check that in the form code, or pass a Boolean argument ByRef that the sub can change; or if you are feeling lazy, just set a public variable in the form that can be checked before closing.