Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 12th 20, 10:45 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 3
Default how to error-check text box values

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

  #2   Report Post  
Old November 15th 20, 06:41 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 532
Default how to error-check text box values

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.
  #3   Report Post  
Old November 15th 20, 06:31 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 3
Default how to error-check text box values

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
  #4   Report Post  
Old November 16th 20, 07:57 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 532
Default how to error-check text box values

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.
  #5   Report Post  
Old November 16th 20, 08:22 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 3
Default how to error-check text box values

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop for text check in three colums and set a text code based on thisin another column [email protected]all.nl Excel Programming 4 April 17th 19 07:33 PM
check if any values on row Norvascom Excel Worksheet Functions 7 March 1st 10 05:56 AM
Loop through column(s) to check values, perform action based on check ward376 Excel Programming 4 November 6th 07 04:21 PM
Check Box Values Jasmine[_2_] Excel Programming 2 May 11th 04 08:25 PM
check variable(text) with cell (text), textbox Mark[_17_] Excel Programming 1 August 27th 03 01:15 PM


All times are GMT +1. The time now is 10:19 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017