VB Textbox only allowing numbers

[sassy]

I have seven VB forms in my project with over a hunderd text boxes. i have the following code for one of my text boxes to not allow anything but numbers. I have two requests that I need some expert advise on...

1. here is my current code:

Private Sub hLine801_Change()
    If hLine801 = vbNullString Then Exit Sub
        If Not IsNumeric(hLine801) Then
            MsgBox "Sorry, numbers only"
            hLine801 = vbNullString
        End If
End Sub

Q: i want to make this same logic for all my text boxes that should only have numbers. Is there any easier way to do this without writing this code for each box. I seriously have over hundred between all seven forms.


2. I also have a text box that I only want a date to be entered.
Q: is there a control that i am not finding that will only allow a certain format like mm/dd/yyyy??

Thanks, in advance!!!

 

[kurchev]

First thoughts, you can greatly simplify if you use a function for the check...

Private Sub hLine801_Change()
     check_isnum(hLine801.Value)
End Sub

Public Sub check_isnum(inputnum)
     If inputnum = vbNullString Then Exit Sub
        If Not IsNumeric(inputnum) Then
            MsgBox "Sorry, numbers only"
            inputnum = vbNullString
        End If
     End If
End Sub

I'm over-simplifying, and I didn't actually test this code in Excel or run it, so there's some debugging needed, but the concept is there. Your biggest problem is that the trigger is the On_Change for the specific field. I don't know of a global On_Change trigger that would look at all text boxes when any text box changes, or one text box when any text box changes.

Anyone else?

 

[MickG]

You can do it with a Class Module, using basically 2 bits of code, are you interested.??
Mick

 

[sassy]

Yes, very. What do I need to do? What is the difference between a regular module and a class module?

 

[MickG]

Hi, Class Modules enable you to create Object and collections with ther own Properties and Methods.
In each of you Seven Userforms you need to Copy the code as below into each of their "UserForm_Initialize()" Events

Private Sub UserForm_Initialize()
Call AllForms(Me)
End Sub

Then you need to create an Ordinary Module, which should look as below when installed:-

Option Explicit
Dim Tbs() As cNumbers
Sub AllForms(nam)
Dim oneControl As Object, pointer As Long
    ReDim Tbs(1 To nam.Controls.count)
    For Each oneControl In nam.Controls
       If TypeName(oneControl) = "TextBox" Then
            pointer = pointer + 1
            Set Tbs(pointer) = New cNumbers
            Set Tbs(pointer).NumTB = oneControl
        End If
    Next oneControl
    ReDim Preserve Tbs(1 To pointer)
End Sub

Then create your Class Module:-
Right Click in the "Project window " of your Vb Editer (Window on the left)
Select "Insert", "Class Module".
A new Module Window will show.
Select the new Module Name in the Project window which will say "Class1".
Click "F4". the properties window will Open and at the top you will see the name "Class1".
Change this to :- cNumber
In the Module window on the right, Paste the code Below.

 Option Explicit
Public WithEvents NumTB As MSForms.TextBox
Private Sub NumTB_Change()
    If NumTB = vbNullString Then Exit Sub
        If Not IsNumeric(NumTB) Then
            MsgBox "Sorry, numbers only"
            NumTB = vbNullString
        End If
End Sub

Close the VB Editer Window.
Open one of the userforms on the sheet and try entering a some data. see if it works !!!
Regards Mick