I have a UserForm with multiple Text Boxes and a "Run" command button. The Text Box values can only be integers. I would prefer to check for non-integer values as they are being typed as opposed to validating them in the "Run" button's "click" handler.

To this end, I've setup "Change" event handlers for the Text Boxes that do the validation and display a MsgBox for illegal entries. Although this works as desired, the user is under no obligation to fix the entry- leaving it unchanged will not trigger a "Change" event. As a result, the "run" button could try to process bad data and an unhandled exception might occur.

I suppose I could just disable the "Run" button if any Text Box contains uncorrected bad data but I'm thinking there is a better design. Any suggestions will be welcome.

Thanks in advance.

-mike

Michael Soyka wrote:

I have a UserForm with multiple Text Boxes and a "Run" command button.
The Text Box values can only be integers. I would prefer to check for
non-integer values as they are being typed as opposed to validating them
in the "Run" button's "click" handler.

To this end, I've setup "Change" event handlers for the Text Boxes that
do the validation and display a MsgBox for illegal entries. Although
this works as desired, the user is under no obligation to fix the entry-
leaving it unchanged will not trigger a "Change" event. As a result,
the "run" button could try to process bad data and an unhandled
exception might occur.

I suppose I could just disable the "Run" button if any Text Box contains
uncorrected bad data but I'm thinking there is a better design. Any
suggestions will be welcome.

Add this to your userform:

Private Function verify(what As MSForms.ReturnInteger) As Integer
Select Case what
Case Asc("0") To Asc("9")
verify = what
Case Else
verify = 0
End Select
End Function

Then add this line to the KeyPress event (e.g. TextBox1_KeyPress) of each
textbox that needs limiting:

KeyAscii = verify(KeyAscii)

--
Some questions have no answers.

On Sunday, November 15, 2020 at 12:41:25 AM UTC-5, Auric__ wrote:
Michael Soyka wrote:

I have a UserForm with multiple Text Boxes and a "Run" command button.
The Text Box values can only be integers. I would prefer to check for
non-integer values as they are being typed as opposed to validating them
in the "Run" button's "click" handler.

To this end, I've setup "Change" event handlers for the Text Boxes that
do the validation and display a MsgBox for illegal entries. Although
this works as desired, the user is under no obligation to fix the entry-
leaving it unchanged will not trigger a "Change" event. As a result,
the "run" button could try to process bad data and an unhandled
exception might occur.

I suppose I could just disable the "Run" button if any Text Box contains
uncorrected bad data but I'm thinking there is a better design. Any
suggestions will be welcome.
Add this to your userform:

Private Function verify(what As MSForms.ReturnInteger) As Integer
Select Case what
Case Asc("0") To Asc("9")
verify = what
Case Else
verify = 0
End Select
End Function

Then add this line to the KeyPress event (e.g. TextBox1_KeyPress) of each
textbox that needs limiting:

KeyAscii = verify(KeyAscii)

--
Some questions have no answers.

Thank you for pointing out that each character could be checked using KeyPress. In my code I check the entire value string using the Like operator and stopped thinking right there.

That said, I tried your approach and found a case where it does not validate, namely values that are pasted into the Text Box. As a separate issue, it appears that neither the paste (^V) nor backspace (^H) keys trigger a KeyPress event. This contradicts the Microsoft documentation for the KeyPress Event that suggests that they will.

I suppose this could be addressed by using the KeyDown event instead since it is designed to report control characters as well as others. Then, if a value is pasted, the ^V code handler could validate the value a character at a time.

-mike

Michael Soyka wrote:

Thank you for pointing out that each character could be checked using
KeyPress. In my code I check the entire value string using the Like
operator and stopped thinking right there.

That said, I tried your approach and found a case where it does not
validate, namely values that are pasted into the Text Box. As a
separate issue, it appears that neither the paste (^V) nor backspace
(^H) keys trigger a KeyPress event. This contradicts the Microsoft
documentation for the KeyPress Event that suggests that they will.

I suppose this could be addressed by using the KeyDown event instead
since it is designed to report control characters as well as others.
Then, if a value is pasted, the ^V code handler could validate the value
a character at a time.

Well, try something like this:

Function verify(what As String) As String
Dim L0, tmp As String, outp As String
For L0 = 1 To Len(what)
tmp = Mid$(what, L0, 1)
Select Case tmp
Case "0" To "9"
outp = outp & tmp
End Select
Next
verify = outp
End Function

....called from the textboxes' Change events:

TextBox1.Text = verify(TextBox1.Text)

--
Maniacal laughter is best done privately.

On Monday, November 16, 2020 at 1:57:53 AM UTC-5, Auric__ wrote:
Michael Soyka wrote:

Thank you for pointing out that each character could be checked using
KeyPress. In my code I check the entire value string using the Like
operator and stopped thinking right there.

That said, I tried your approach and found a case where it does not
validate, namely values that are pasted into the Text Box. As a
separate issue, it appears that neither the paste (^V) nor backspace
(^H) keys trigger a KeyPress event. This contradicts the Microsoft
documentation for the KeyPress Event that suggests that they will.

I suppose this could be addressed by using the KeyDown event instead
since it is designed to report control characters as well as others.
Then, if a value is pasted, the ^V code handler could validate the value
a character at a time.
Well, try something like this:

Function verify(what As String) As String
Dim L0, tmp As String, outp As String
For L0 = 1 To Len(what)
tmp = Mid$(what, L0, 1)
Select Case tmp
Case "0" To "9"
outp = outp & tmp
End Select
Next
verify = outp
End Function

...called from the textboxes' Change events:

TextBox1.Text = verify(TextBox1.Text)

--
Maniacal laughter is best done privately.

My approach uses the "Like" operator:
tb.Value Like String(Len(tb.Value), "#")
which yields a pass/fail decision but expects the user to fix it.

Your solution removes the bad characters and thwarts stubborn users- better.

Thanks for the conversation.