PDA

View Full Version : [SOLVED] Textbox validation



Chunk
08-24-2016, 06:53 AM
I have a userform that contains 14 textboxes (tb_one, tb_two, tb_three..........tb_fourteen).

The values that the user needs to enter into the text boxes are numbers. (no decimals, special characters or letters). I do not want a limit on the number of characters as well. After the user enters the numbers they will click on a "Calculate" button that will preform some simple math. For the math to happen, I need to ensure that only numbers are entered into the textboxes. Here is what I have been attempting (obviously, with no success):


Private Sub tb_one_AfterUpdate()
IntCheck
End Sub
Private Sub tb_two_AfterUpdate()
IntCheck
End Sub

Public Sub tb_three_AfterUpdate()
IntCheck
End Sub
Private Sub tb_four_AfterUpdate()
IntCheck
End Sub
Private Sub tb_five_AfterUpdate()
IntCheck
End Sub
Private Sub tb_six_AfterUpdate()
IntCheck
End Sub
Private Sub tb_seven_AfterUpdate()
IntCheck
End Sub
Private Sub tb_eight_AfterUpdate()
IntCheck
End Sub
Private Sub tb_nine_AfterUpdate()
IntCheck
End Sub
Private Sub tb_ten_AfterUpdate()
IntCheck
End Sub
Private Sub tb_eleven_AfterUpdate()
IntCheck
End Sub
Private Sub tb_twelve_AfterUpdate()
IntCheck
End Sub
Private Sub tb_thirteen_AfterUpdate()
IntCheck
End Sub
Private Sub tb_fourteen_AfterUpdate()
IntCheck
End Sub

Private Sub IntCheck()
If TypeName(Me.ActiveControl) = "TextBox" Then
With Me.ActiveControl
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
.Value = vbNullString
End If
End With
End If
End Sub


I stepped through the code and it seems that once it gets to the "If TypeName(Me.ActiveControl) = "TextBox" Then" line, it goes right to "End if". Any help provided is greatly appreciated. Thanks in advance.

Chunk

mikerickson
08-24-2016, 07:37 AM
You could do this with a class module.
In the VB editor insert a class module (it should be named Class1)
Then put this code into t

' in Class1 module

Dim myColl As Collection
Public WithEvents TextBox As MSForms.TextBox

Private Sub Class_Initialize()
Set myColl = New Collection
myColl.Add Item:=Me
End Sub

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

Then put code like this in the Intialize event of the user form so that indicated text boxes will only accept numeral input. You can expand the array to list all the text boxes you want.
(you can delete all those AfterUpdate events)


' in userform code module

Private Sub UserForm_Initialize()
Dim newBox As Class1
Dim oneTextBox As Variant

For Each oneTextBox In Array(tb_one, tb_two, tb_three)
Set newBox = New Class1
Set newBox.TextBox = oneTextBox
Next oneTextBox

Set newBox = Nothing
End Sub

mikerickson
08-24-2016, 08:01 AM
To get back to the question of why the ActiveControl is acting that way:

I suspect that your text boxes are in either a Frame or a MultiPage control.

If TextBox1 is in Frame1, and TextBox1 has the focus, in that situation Userform1.ActiveControl is Frame1, while Frame1.ActiveControl is TextBox1.

The ActiveControl property does not drill down through Frames or MultiPages.

You could add this to the user form to drill down to the control that has the focus


Property Get ActiveControlWithFocus() As MSForms.Control
Set ActiveControlWithFocus = Me.ActiveControl
On Error Resume Next
Do
If TypeName(ActiveControlWithFocus) = "MultiPage" Then
Set ActiveControlWithFocus = ActiveControlWithFocus.Pages(ActiveControlWithFocus.Value)
End If
If ActiveControlWithFocus.Controls.Count = 0 Then Exit Property
Set ActiveControlWithFocus = ActiveControlWithFocus.ActiveControl
Loop Until Err
End Property

Your like from IntCheck would become

If TypeName(ActiveControlWithFocus) = "TextBox" Then

Chunk
08-24-2016, 08:01 AM
He shoots, he scores....works perfect. Thanks for the help Mike
The textbox is indeed on a tabbed frame.
Chunk