TextBox entry required. How to code?

[underpressure]

TextBoxes 4 thru 21 require a numeric value to properly process the procedure.
Instead of inserting 18 instances of this code, how can it be written to loop thru the 18 textboxes and set focus on the appropriate TextBox?
Even better, MsgBox would indicate the missing value (i.e.: "Value Required for #6")

If TextBox4.Value = "" Then
TextBox4.SetFocus
 MsgBox "Value Required", vbExclamation
Exit Sub
End If
 
If TextBox5.Value = "" Then
TextBox5.SetFocus
MsgBox "Value Required", vbExclamation
Exit Sub
End If
 
‘etc
‘etc

 

[dmt32]

Hi,
with so many textboxes best to create a class & manage this way. Also, rather than having a msgbox pop-up for an invalid entry allow only numeric values to be entered.

Try following

From VBA editor, Insert a Class Module & name it TextBoxClass << ENSURE YOU DO THIS

Place following code in the class module

Public WithEvents TextBoxClass As MSForms.TextBox


Private Sub TextBoxClass_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    Case 46, 48 To 57
'valid entries [0-9] "."
    Case Else
'cancel
        KeyAscii = 0
    End Select
End Sub

Add Following to your UserForms Code Page

Dim TextBox() As New TextBoxClass


Private Sub UserForm_Initialize()
    Dim Ctrl As Control
    Dim Count As Integer
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" And Val(Mid(Ctrl.Name, 8)) > 3 And Val(Mid(Ctrl.Name, 8)) < 22 Then
            Count = Count + 1
            ReDim Preserve TextBox(1 To Count)
            Set TextBox(Count).TextBoxClass = Ctrl
        End If
    Next
End Sub

note the declared variable in red - This MUST site at TOP of your forms code page OUTSIDE any procedure.

When you run the form you should find that your textboxes named textbox4 thru to textbox21 you can only enter numeric values

Hope helpful

Dave

 

[underpressure]

Dave,
Thanks for the assistance.
It's taken me awhile to get back to this.
I'll try implementing your suggestion and test it out.
Looks like a great solution.