Apply code to multiple textbox controls

[AndyEd]

HI, I am using the following code to limit textboxes to only allow the input of numbers and "/" to accommodate dates. The code then inputs the "/" after every second digit, to provide the following format "xx/xx/xx".

I'm having to apply this to a large number of textboxes, which makes for lots of duplication.

Can anyone suggest a workaround?

I would love to use a date picker instead of the manual textbox entry, however the machines are locked down to the point that I can't import any addins or anything like that. UNless anyone can suggest an alternative, or a way to check a valid date is being entered?

Thanks in advance.

Private Sub txt4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Select Case KeyAscii
        Case 48 To 57, 47
        Case Else
            KeyAscii = 0
    End Select

End Sub

Private Sub txt4_Change()

    Dim TextStr As String

    With Me.txt4
        TextStr = .Text

        If (Len(TextStr) = 3 And Mid(TextStr, 3, 1) <> "/") Then
            TextStr = Left(TextStr, 2) & "/" & Right(TextStr, 1)
        ElseIf (Len(TextStr) = 6 And Mid(TextStr, 6, 1) <> "/") Then
            TextStr = Left(TextStr, 5) & "/" & Right(TextStr, 1)
        End If

            .Text = TextStr

End Sub

 

[dmt32]

HI, I am using the following code to limit textboxes to only allow the input of numbers and "/" to accommodate dates. The code then inputs the "/" after every second digit, to provide the following format "xx/xx/xx".

I'm having to apply this to a large number of textboxes, which makes for lots of duplication.

Can anyone suggest a workaround?

I would suggest rather than checking each textbox for date / format entry in turn, do it all in one go from a single code when user presses your commandbutton
Code can also correct any valid date entry to your preferred format

see if following helps you

Place in STANDARD module

Function IsValidDateEntry(ByVal Form As Object, ParamArray DateTextBoxes() As Variant) As Boolean
        Dim DateBox As Variant
        Dim InvalidDate As Boolean
        
        Const RequiredFormat  As String = "dd/mm/yy"
        
        With Form
        For Each DateBox In DateTextBoxes
            With DateBox
                If IsDate(.Value) Then
                    .Value = Format(DateValue(.Value), RequiredFormat)
                    .BackColor = vbWhite
                Else
                    .BackColor = vbRed
                If Not InvalidDate Then InvalidDate = True: .SetFocus
                End If
            End With
        Next DateBox
        End With
        
        If InvalidDate Then MsgBox "Please Enter Valid Date(s)", 48, "Invalid Date Entry"
        
        IsValidDateEntry = Not InvalidDate
End Function

Change Required Format where shown in RED

To Call from your UserForm CommandButton

Private Sub CommandButton1_Click()
    If Not IsValidDateEntry(Me, Me.TextBox1, Me.TextBox2) Then Exit Sub
    
    'rest of code
End Sub

Add all then the TextBox Objects you want to check where shown in RED

Users can enter a valid date in ANY format & code should change it to your required format

Dave

 

[AndyEd]

Hi Dave

That seems to work fine, thank you.

How do you code to disregard and blank textboxes?

Thanks,

Andy

 

[AndyEd]

I have also discovered that when the CommandButton is on a different page (multipage form) to the first TextBox to display an invalid date, it can't set the focus, causing an error on the below line,

If Not InvalidDate Then InvalidDate = True: .SetFocus

I can remove the : .SetFocus part and it works, but I liked the idea of the code taking you to the offending control.

How might I go about resolving that?

Thank you

 

[dmt32]

I can remove the : .SetFocus part and it works, but I liked the idea of the code taking you to the offending control.

try this update

Function IsValidDateEntry(ByVal Form As Object, ParamArray DateTextBoxes() As Variant) As Boolean
    Dim DateBox     As Variant
    Dim InvalidDate As Boolean
    
    Const RequiredFormat  As String = "dd/mm/yy"
    
    With Form
        For Each DateBox In DateTextBoxes
            With DateBox
                If IsDate(.Value) Then
                    .Value = Format(DateValue(.Value), RequiredFormat)
                    .BackColor = vbWhite
                Else
                    If Len(.Value) > 0 Then
                        .BackColor = vbRed
                        If Not InvalidDate Then InvalidDate = True
                    Else
                      .BackColor = vbWhite
                    End If
                End If
            End With
        Next DateBox
    End With
    
    If InvalidDate Then MsgBox "Please Enter Valid Date(s)", 48, "Invalid Date Entry"
    
    IsValidDateEntry = Not InvalidDate
End Function

Dave

 

[AndyEd]

Works a treat. Thank you Dave.

 

[dmt32]

Works a treat. Thank you Dave.

most welcome & appreciate your feedback

Dave

 

[AndyEd]

Hi Dave

Sorry, was just going through my validation steps and I noticed that I have coded some date TextBoxes to display "NA" if they are not required. How can I get your code to ignore any where value = "NA"?

Thanks,

Andy

 

[dmt32]

Sorry, was just going through my validation steps and I noticed that I have coded some date TextBoxes to display "NA" if they are not required. How can I get your code to ignore any where value = "NA"?

change this line

If Len(.Value) > 0 Then

to this

If Len(.Value) > 0 And .Value <> "NA" Then

Dave

 

[AndyEd]

Thanks again Dave.

Andy