Validating textbox entry on Userform (Excel VBA)

Question

I have a textbox (in this instance it is textbox11) where the front end user will need to input "Half Day" in some form or another.

Here is my code:

Private Sub TextBox11_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Me.TextBox11 = "half" Or Me.TextBox11 = "half day" Or Me.TextBox11 = "half-day" Or Me.TextBox11 = 0.5 Or Me.TextBox11 = "1/2" Then
Me.TextBox11 = "Half Day"
End If

End Sub

When I run it, it works wonderfully - for the conditions I have set. However, if a user, say, inputs "hALf dAy", then this will not be validated and fixed to standard form as it is not on my condition list.

There is noway where I could put all permutations of what a user might put in. Is there a way to make this case-insensitive? So no matter what case a user puts in, it will check against my conditions anyway.

Thanks

LCase the user input, but if you really want validation the best option is to use a ComboBox with predefined values rather than a text box where the user can type any ol' garbage. — David Zemens
– David Zemens, Commented Jun 24, 2018 at 14:03
I did think of putting a ComboBox, but it is more complicated in this instance, as this userform already displays data on the sheet. So it is easier to put in a Textbox to display what is on the sheet. -- So do I put the LCase like this "if Me.Textbox11 = LCase("half")....."? — Oday Salim
– Oday Salim, Commented Jun 24, 2018 at 14:05
no, you Lcase(Me.TextBox11.Text) and compare it to the (already lower-cased) text strings of allowed values, like "half". By converting all characters to lower case, and then comparing to your lower-case strings, you are effectively case-insensitive. — David Zemens
– David Zemens, Commented Jun 24, 2018 at 14:07
I'm not sure I follow why it's easier to use a TextBox versus a ComboBox. Why are you using a Form to read data from the worksheet? Like, you're using a Worksheet as a primary UI and then somehow also passing that information into a UserForm where the user has to presumably do more stuff? Seems redundant IMO. But that's a separate topic about optimizing the code, rather than just fixing the immediate problem... — David Zemens
– David Zemens, Commented Jun 24, 2018 at 14:09
The other option is to force in-cell Validation on the worksheet itself, with a Validation list. That way, when the form reads the value from the Worksheet, you can be certain that it is already an allowable value. — David Zemens
– David Zemens, Commented Jun 24, 2018 at 14:11

David Zemens · Accepted Answer · 2018-06-24 14:06:04Z

1

LCase (or UCase) the user input. By converting all characters to lower case, and then comparing to your lower-case strings, you are effectively case-insensitive. Here's an example of that.

Private Sub TextBox11_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Select Case Trim(LCase(Me.TextBox11.Text))
    Case "half", "half day", "half-day", "0.5", ".5", "1/2"
        Me.TextBox11.Text = "Half Day"
    Case Else
        ' do nothing?
        '
        '
End Select

But if you really want validation the best option is to use a ComboBox with predefined values rather than a text box where the user can type any ol' garbage.

answered Jun 24, 2018 at 14:06

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Validating textbox entry on Userform (Excel VBA)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related