Restricting user input in text box in excel 2

[smurf01]

Hi,
Is there any way i can restrict the data enetered into a textbox in excel. I have a text box that requires six digits then a forward slash and then another digit(see example), if the data is not entered exactly as shown i want to display a message box telling the user that they cannot continue until the data is valid.

example = 192837/1

Any help would be appreciated

 

[bryanbayfield]

Option Explicit
Private Sub TextBox1_Change()
Select Case Len(TextBox1)
    Case 1 To 6, 8
        If Not IsNumeric(Right(TextBox1, 1)) Then
            MsgBox "Entry must be in the format:" & vbCrLf & _
                        "######/# where # is a single digit"
            TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
            End If
    Case 7
        If Not Right(TextBox1, 1) = "/" Then
            MsgBox "A forward slash must come after 6 digits"
            TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
            End If
    Case Else
        If Len(TextBox1) > 8 Then: MsgBox "You've entered too many characters." _
                            & vbCrLf & "Entry must be 8 characters long"
    End Select
End Sub

This did the job for me for a text box called TextBox1 that was embedded in a worksheet. I used the worksheets event pull down menus to select teh text box change event.

You should be able to adapt it for your own needs.

Bryan.

PS: Hope it's not too late!

 

[smurf01]

Bryan thank you very much i will try it out and let you know

Paul

 

[rmikesmith]

Bryan,

Nice routine! I'd like to make one suggestion. Use the LostFocus event in addition to the Change event to prevent a user from moving away w/o entering the correct data/format. Paul, if you're still out there...

Private Sub TextBox1_LostFocus()
  If Not (TextBox1.Text Like "######/#") Then
    TextBox1.Activate
    MsgBox "Entry must be in the format:" & vbCrLf & _
           "######/# where # is a single digit.", vbExclamation + vbOKOnly, "Input Error"
  End If
End Sub

Regards,
Mike

 

[rmikesmith]

After posting my suggested code I realized the event handler should probably exit if nothing has been typed into the TexBox. This will allow users to navigate out of the TextBox. Just add the following line to the beginning of the event procedure:

If Len(TextBox1.Text) = 0 Then Exit Sub

Regards,
Mike

 

[smurf01]

Mike thanks for the further info

Paul