Allow numbers only across whole userform 1

[2ks]

I have a user form with 112 text boxes.

Is there an easy way to make the whole form numbers only without programming every text box..I can do numbers only by text box

Thanks

 

[SkipVought]

hi,

only without programming

How do you expect a userform to function 'without programming?' I am curious!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[2ks]

Hi Skip

Thanks for responding

I was hoping not to have to program all 112 text boxe, rather program some type of routine that would effectively program all txt boxes to behave and accept numerical when the userform is opened opening.

1. User form opened
2. Routine that programs everything in user form to accept numbers only

If it cannot be done then fair enough...The long slog version it will have to be

ps...How do we contribute financially to this site

 

[SkipVought]

How do we contribute financially to this site

There is a | [red]Donate[/red] | link below.

100+ text boxes is a lot! What are you trying to do?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

I am intensely curious myself. A userform with 112 textboxes? This sounds outrageous. Hopefuly these are on a MultiPage. The idea of 112 textboxes on a userform gives me the willies.

How exactly are you programming them to be numbers? After all, these are TEXT boxes. Not numeric boxes.

Gerry

 

[MarkWalsh]

Assuming that you have a valid reason for that many textboxes in a form (and that you want to run the exact same code for each textbox), I think the way to do what you want would be to create a Class module (e.g. call it 'TextBoxHandler') to handle the text box, add a property (defined withEvents) to hold a reference to the textbox:

Private WithEvents mTextBox As TextBox

Add the code you want to use for the textbox to the class for the event (e.g. KeyDown), then when the form initializes, loop through all of the controls and create a new 'TextBoxHandler' object for each TextBox. Add each newly created object to an array/collection/dictionary.

 

[macropod]

Hi 2ks,

The following code, attached to a textbox (TextBox1) on a UserForm, ensures that only numbers can be entered. Any non-numeric keystroke generates a beep and the offending character is deleted.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii > 47 And KeyAscii < 59) Then
	Beep
	KeyAscii = 0
End If
End Sub

You'll need to attach such code to every textbox you want to test. Regardless of whether you have the whole routine attached to each textbox or a simple call statement to a generic function that does the work, you can't get around that. The latter approach might look like:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
TestChars KeyAscii
End Sub

...

Private Sub TextBox112_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
TestChars KeyAscii
End Sub


Function TestChars(KeyAscii)
If Not (KeyAscii > 47 And KeyAscii < 59) Then
  Beep
  KeyAscii = 0
End If
End Function

Cheers
[MS MVP - Word]

 

[2ks]

Thanks everyone for the humour and advice.

I know what fumei means as the userform with all those txtboxes does indeed look scary but its no different really to a table when you look at it.

In reality only 10% of the boxes will be needed by each input so its not all bad.

I already knew about macropod first advice but the 2nd example should work a treat.

You all have a good un

Thanks

 

[SkipVought]

In reality only 10% of the boxes will be needed by each input so its not all bad.

You might want to consider a context sensitive adding of textboxes, as needed.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkWalsh]

Function TestChars(KeyAscii)
If Not (KeyAscii > 47 And KeyAscii < 59) Then
  Beep
  KeyAscii = 0
End If
End Function

Be aware that this will kill EVERY key except numbers - including the tab, delete, enter keys, the arrow keys, etc. Make sure you check for and allow any other keys you want the user to be able to use.

 

[macropod]

Hi Mark,

Be aware that this will kill EVERY key except numbers

That was the intention and matches the specs given by 2ks:

Is there an easy way to make the whole form numbers only

Cheers
[MS MVP - Word]

 

[MarkWalsh]

My mistake. I misread your message, and had initially thought that you had used the 'KeyDown' event instead of the 'KeyPress' event. Adding that code to the 'KeyDown' will prevent the navigation keys from working as well.

 

[JimHilton]

Hi, while looking for something totally un-related to your issue, I found this freebie ocx that might just save you a pant load of work. Here is the blurb:


AdTextBox OCX 1.0 description

AdTextBox OCX 1.0 comes as an essential and effective textbox featuring validation control, input key control, letter case input control, and other integrated features. It is useful for programmers who don't want to reprogram the same code every time.

Ooops I left the page, but just google the ocx name.

Hope that helps.

And say, if you find an OCX that can hyphenate and justify I be mighty thankful!

Jim

 

[fumei]

Yeah, if 10% of the textboxes are generally used, then I would seriously try to make things context sensitive.

Gerry