Search code examples
excelvbauserform

Vba user forms textboxes

Looking for guidance on how to reduce my code instead of having lots of codes essential doing the same thing.

I have a form with 30+ text boxes (all textboxes have been renamed but all of them do start with txt_) and at the moment I have some code that does some basic validation to ensure it’s numeric only and has no spaces which is validated on exit of the textbox.

But as you could imagine this same piece of code is basically copied and pasted in to each text box private sub txt_

Example of repeated code:

Private Sub txt_Quoted_Exit(ByVal cancel As MSForms.ReturnBoolean)

If Not IsNumeric(txt_Quoted.Value) Or InStr(txt_Quoted, “ “) > 0 Then
 Msgbox “Only Numbers Allowed”
End If

End Sub

Thanks for any advice / help given.

Solution

  • Try this. All your textboxes can Call the same Sub and pass their value into the ValidationSub

    Private Sub txt_Quoted_Exit(ByVal cancel As MSForms.ReturnBoolean)
    
Call TestSub(txt_Quoted.Value)

End Sub

Private Sub ValidationSub(Txt As String)

If Not IsNumeric(Txt) Or InStr(Txt, “ “) > 0 Then
 Msgbox “Only Numbers Allowed”
End If

End Sub