Need to check 102 fields if they are numeric

[selant]

Hi friends,

I have got 102 quantity of TextBoxes in my form.

Each field represents the number of "SALE QUANTITY" , "FREE GIVEN" , "RETURNED" products. We have got 34 PRODUCTS range. (34 x 3 fields = 102 textfields)


The name of the TextBox fields are as follows :

PR01S , PR01B , PR01K

PR02S , PR02B , PR02K

...

...

PR34S , PR34B , PR34K


All of the fields are required to be numeric data only. How can i check this fields to be numbers on the same time of the data input (or when jumping to the next field. There will be a message box mentioning that the field can only contain numbers + SetFocus to the wrong input field) ?

I dont want to write same code for each item, its too hard and long..

If i can solve this problem, then I will need help to enter the values from this fields to the worksheet because I must find a way to enter the values with a for next loop or something.. I dont want to do it one by one.

Any helps and code samples will greatly be appreciated.

Here is how the form looks :

P.S : I dont know if there is a possibility to exclude the product to be entered in the worksheet if all 3 boxes are Zero (0) for that specific item.

Example : If all fields are "0" for item "Coke" then I dont want this products information entered in the excel worksheet.

0 Sale , 0 Free, 0 Return

 

[bobbly_bob]

Hi Selant,

is setting the allowed field values in data validation to whole numbers only of any use to you?

 

[selant]

Hi Selant,

is setting the allowed field values in data validation to whole numbers only of any use to you?

Hi, the fields are textfields so I must prevent the mistaken Text inputs, i only want to allow numeric value in the fields and of course they must be whole numbers, no decimals allowed. Thank you

 

[Colin Legg]

Hi Selant,

To be sure, is this an Excel VBA userform? Or are you using VB6 or .Net etc... ?

 

[mikerickson]

Create a class module, use the propeties window to name it clsNumericTextBox and put this code in it.

Option Explicit

Public WithEvents NumericBox As MSForms.TextBox

Private Sub NumericBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Chr(KeyAscii) Like "[!0-9]" Then
        KeyAscii = 0
    End If
End Sub

Then in the userform's code module, declare a module wide array and this Initialize code

Option Explicit
Dim myNumericBoxes() As clsNumericTextBox

Private Sub UserForm_Initialize()
    Dim i As Long
    ReDim myNumericBoxes(1 To 102)
    For i = 1 To 102
        Set myNumericBoxes(i) = New clsNumericTextBox
        Set myNumericBoxes(i).NumericBox = Me.Controls("TextBox" & i)
    Next i
End Sub

Then when the user will only be able to enter numerals into the text boxes.
This code assumes your textboxes are named TextBox1, TextBox2, etc. if your naming scheme is different the code will need to be adjusted.

 

[mikerickson]

I re-read the OP, if your textboxes are named PR01S,PR01B, PR01K , etc, the loop in the intialize routine should be changed to

For i = 1 to 34
      Set myNumericBoxes(i) = New clsNumericTextBox
      Set myNumericBoxes(i).NumericBox = Me.Controls("TextBox" & Format(i,"00S"))
      Set myNumericBoxes(i + 34) = New clsNumericTextBox
      Set myNumericBoxes(i + 34).NumericBox = Me.Controls("TextBox" & Format(i,"00B"))
      Set myNumericBoxes(i + 68) = New clsNumericTextBox
      Set myNumericBoxes(i + 68).NumericBox = Me.Controls("TextBox" & Format(i,"00K"))
Next i

 

[Norie]

MIke

Why are you creating multiple, new instances of the class?

I've not worked with classes for some time but if I recall you should only need one instance of the class.

Then you 'add' each textbox to that new class.

I'm afraid I can't check that out though - I used to have a folder full of class examples but I think the computer must have got hungry one night.

 

[mikerickson]

Each of the text boxes has to be a property of the class. One could create a class with a property that is an array, but it seemed that the one instance=one textbox was easiest to code and easiest to understand.

As I worked with this, I found that Mac VBA (v.5?) doesn't like an array as Public WithEvents.

If you were thinking of Public WithEvents MyBoxes as Collection, the events avaliable would be those of a Collection not a text box.

 

[Norie]

Mike

Perhaps I'm missing something but aren't you creating 102 instances of the class?

Here's one example I was finally able to unearth.

This was in the class module which was called CTextboxes.

Option Explicit
Public WithEvents TextGroup As MSForms.TextBox
Private Sub TextGroup_Change()
    TextGroup.Value = UCase(TextGroup.Value)
End Sub

And this was in the userform module.

Dim TextBoxes() As New CTextboxes
Private Sub CommandButton1_Click()
    Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim I As Long
    I = 1
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            ReDim Preserve TextBoxes(1 To I)
            Set TextBoxes(I).TextGroup = ctl
            I = I + 1
        End If
    Next ctl
    
End Sub

I don't know if this is the correct way to approach this type of thing but appears to work.

 

[mikerickson]

UBound of the array TextBoxes would be 102. The loop is different, but both codes have the same number of instances of the UD class.

Each of the textbox's has to be "connected" to an instance of the line "Public WithEvents ..."

Nether an array nor a collection inherit the properties of its members.
I suggest, that implies that there must be at least one instance of the UD class for each text box.

To state my hypothisis formally:
"When class modules are used to provide common event code for controls, the number of instances of user defined class objects is greater than or equal to the number of controls that trigger the common event code."