Userform text box validation for numbers only - no characters

[jes82]

Hi,

I wanted to set up a validation that would ensure that only a number was entered and if not then a msg box will pop up to prompt them.

The text box will be a series of numbers varying in length each time it is entered

At the moment I have the following code:

If Not IsNumeric(txtRefNo.Value) Then
MsgBox "Please enter the organisation number", vbExclamation + vbOKOnly, ""

However, this still allows the following characters to be entered: + - . , $

Can I write a code that will search for these characters within the textbox and prompt the user to correct their input if it doesn't include just numbers

Thanks,
J

 

[Peter_SSs]

Welcome to the MrExcel board!

Does having this txtRefNo_Change code in your UserForm code do what you want?
Note that as is the code produces the message box if the text box is cleared of an existing value too, and that may not be what you want?

Private Sub txtRefNo_Change()
 With CreateObject("VBScript.RegExp")
    .Pattern = "^\d+$"
    If Not .Test(UserForm2.txtRefNo.Value) Then
      MsgBox "Please enter the organisation number", vbExclamation + vbOKOnly, ""
    End If
  End With
End Sub

 

[tlowry]

For i = 1 To Len(txtRefNo)
        If Not Mid(txtRefNo, i, 1) Like "[0-9]" Then _
            MsgBox "Please enter the organisation number", vbExclamation + vbOKOnly, ""
    Next i

 

[jes82]

Hi,

Thank you both for your replies.

I have tried both methods but neither worked unfortunately.

Sorry, i don't think i was clear enough to begin with. Initially this text box is on the first page of a mutlipage userform and text box is there to provide a unique identifier for each participant in the survey (which is the purpose of the userform). The user will enter their unique identifier and then press next. What I wanted the next button to do was check and make sure only a number was provided and if that is the case go to the next page of the userform and if not then a msgbox will pop up.

Here is the full code so far:

Private Sub cmdBegin_Click()
'this function ensures org number is recorded before proceeding with survey

If Not IsNumeric(txtRefNo.Value) Then
MsgBox "Please enter the organisation number", vbExclamation + vbOKOnly, ""
Else
clearPages
MultiPage1.Pages("Step1").Visible = True
MultiPage1.Pages("Instructions").Visible = False
MultiPage1.Pages("Instructions").Visible = True
questionTwoVisible (False)
questionThreeVisible (False)
End If

End Sub

I have tried to work both codes into this code already however, neither was working. If you could edit the code to fit into the one above that would be greatly appreciated!

Thanks,
J

 

[tlowry]

I was thinking "It's better to stay out than to get out" (Mark Twain). However, if you want to wait for a button, then I suggest: (I've indicated changes with bold.)

ps. Please mark your code using the # button.

Private Sub cmdBegin_Click()
'this function ensures org number is recorded before proceeding with survey
    
    [B]If CheckInput(txtRefNo.Value) = False Then[/B]
        MsgBox "Please enter the organisation number", vbExclamation + vbOKOnly, ""
    Else
        clearPages
        MultiPage1.Pages("Step1").Visible = True
        MultiPage1.Pages("Instructions").Visible = False
        MultiPage1.Pages("Instructions").Visible = True
        questionTwoVisible (False)
        questionThreeVisible (False)
        MsgBox "OK"
    End If

End Sub

[B]Function CheckInput(IDin) As Boolean
    CheckInput = False
    Dim i
    If Len(IDin) = 0 Then Exit Function
    For i = 1 To Len(IDin)
        If Not Mid(IDin, i, 1) Like "[0-9]" Then Exit Function
    Next i
    CheckInput = True
End Function
[/B]

 

[jes82]

I was thinking "It's better to stay out than to get out" (Mark Twain). However, if you want to wait for a button, then I suggest: (I've indicated changes with bold.)

ps. Please mark your code using the # button.

Private Sub cmdBegin_Click()
'this function ensures org number is recorded before proceeding with survey
    
    [B]If CheckInput(txtRefNo.Value) = False Then[/B]
        MsgBox "Please enter the organisation number", vbExclamation + vbOKOnly, ""
    Else
        clearPages
        MultiPage1.Pages("Step1").Visible = True
        MultiPage1.Pages("Instructions").Visible = False
        MultiPage1.Pages("Instructions").Visible = True
        questionTwoVisible (False)
        questionThreeVisible (False)
        MsgBox "OK"
    End If

End Sub

[B]Function CheckInput(IDin) As Boolean
    CheckInput = False
    Dim i
    If Len(IDin) = 0 Then Exit Function
    For i = 1 To Len(IDin)
        If Not Mid(IDin, i, 1) Like "[0-9]" Then Exit Function
    Next i
    CheckInput = True
End Function
[/B]

Thank you tlowry, it worked perfectly!!
Sorry, I am very new to this forum (and excel vba) - how do you mark the code?

 

[Rick Rothstein]

Here is the full code so far:

Private Sub cmdBegin_Click()
'this function ensures org number is recorded before proceeding with survey

If Not IsNumeric(txtRefNo.Value) Then
MsgBox "Please enter the organisation number", vbExclamation + vbOKOnly, ""
Else
clearPages
MultiPage1.Pages("Step1").Visible = True
MultiPage1.Pages("Instructions").Visible = False
MultiPage1.Pages("Instructions").Visible = True
questionTwoVisible (False)
questionThreeVisible (False)
End If

End Sub

Try replacing the If..Then statement with this one...

If txtRefNo.Value Like "*[!0-9]*" Or Len(txtRefNo.Value) = 0 Then

 

[tlowry]

• Paste the code
• Select the code
• Click the button "#"

 

[jes82]

Great, thanks!