Multipage userForm question re: tab

leopardhawk

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, I hope I can explain this so it makes sense.

I have a multipage userform with two pages, each page has 7 text boxes and 3 combo boxes. Everything works fine but I just noticed during some testing that when I hit the 'tabkey' to move away from the last textbox (which happens to be a textbox where the user enters a date), the date does NOT reformat to a date format like it does with the other textboxes where dates are entered. Not sure if it matters but the next item that the 'tab' key takes you to is the OK commandbutton. The 'tabindex' starts at 0 for the first textbox and goes up to 9 for all of the boxes on the userform.

I did notice however, that if I keep hitting the tabkey until the cursor is back in the first textbox on the userform, THEN the date in the last textbox formats correctly! I am hoping that there is a simple solution to this and that formatting the last textbox as soon as you tab away from it can and will occur. I hope someone has a suggestion for me. The code for the textbox in question is below.

VBA Code: 
Private Sub OASTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With OASTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Odd. The Exit event of the last control on a Page doesn't fire. Try moving your formatting code to the AfterUpdate event
 
Upvote 0
@mikerickson thanks for your suggestion. Being a bit of a noob, I'm unsure how to proceed. I don't have any AfterUpdate events in my code. It's weird that the date will format if I tab my way back to the first textbox so something is preventing it when you first tab away from the last textbox.

To show the context, I am posting the whole VBA for this particular userform. The two textboxes in question are OASTextBox and SOASTextBox. Like I said, everything works great except for this one little snag...!!

VBA Code: 
Private Sub CloseCommandButton_Click()

Unload Me

End Sub

Private Sub GenderComboBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.GenderComboBox
      If .Text = "M" Or .Text = "F" Or .Text = "" Then
         .BackColor = rgbWhite
         Label4.Caption = "Gender"
         Label4.ForeColor = rgbBlack
      Else
         Label4.Caption = "Please select M or F"
         Label4.ForeColor = RGB(255, 55, 55)
         .BackColor = RGB(255, 55, 55)
         GenderComboBox.Value = ""
      End If
   End With
End Sub

Private Sub SGenderComboBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.SGenderComboBox
      If .Text = "M" Or .Text = "F" Or .Text = "" Then
         .BackColor = rgbWhite
         Label24.Caption = "Gender"
         Label24.ForeColor = rgbBlack
      Else
         Label24.Caption = "Please select M or F"
         Label24.ForeColor = RGB(255, 55, 55)
         .BackColor = RGB(255, 55, 55)
         SGenderComboBox.Value = ""
      End If
   End With
End Sub

Private Sub UserForm_Initialize()
Dim objControl As MSForms.Control

    For Each objControl In Me.Controls
        If TypeName(objControl) = "TextBox" And objControl.Tag <> "" Then
           Me.setupPlaceholder objControl.Name, False
        End If
    Next objControl

    MultiPage1.Value = 0
    Me.FirstNameTextBox.SetFocus
        
    Me.GenderComboBox.List = Array("M", "F")
    Me.GenderComboBox.Style = fmStyleDropDownCombo
    
    Me.OptionComboBox.List = Array("100% Joint Life", _
        "100% Joint Life 5-year guarantee", "100% Joint Life 10-year guarantee", "100% Joint Life 15-year guarantee", _
        "75% Joint Life 5-year guarantee", "75% Joint Life 10-year guarantee", "75% Joint Life 15-year guarantee", _
        "60% Joint Life 5-year guarantee", "60% Joint Life 10-year guarantee", "60% Joint Life 15-year guarantee", _
        "Single Life", _
        "Single Life 5-year guarantee", "Single Life 10-year guarantee", "Single Life 15-year guarantee", _
        "Other")

    Dim LastRow As Long
    Dim SheetName As String
    SheetName = "Sheet20"
    LastRow = Sheets(SheetName).Cells(Rows.Count, "A").End(xlUp).Row
    Me.ProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    Me.SProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    
    Me.SGenderComboBox.List = Array("M", "F")
    Me.SGenderComboBox.Style = fmStyleDropDownCombo
    
    Me.SOptionComboBox.List = Array("100% Joint Life", _
        "100% Joint Life 5-year guarantee", "100% Joint Life 10-year guarantee", "100% Joint Life 15-year guarantee", _
        "75% Joint Life 5-year guarantee", "75% Joint Life 10-year guarantee", "75% Joint Life 15-year guarantee", _
        "60% Joint Life 5-year guarantee", "60% Joint Life 10-year guarantee", "60% Joint Life 15-year guarantee", _
        "Single Life", _
        "Single Life 5-year guarantee", "Single Life 10-year guarantee", "Single Life 15-year guarantee", _
        "Other")

End Sub

Private Sub MultiPage1_Change()

    GetTextBoxByTabIndex(Me.MultiPage1.SelectedItem, 0).SetFocus

End Sub

Private Function GetTextBoxByTabIndex(ByVal ControlParent As Object, ByVal TabIndex As Long) As Control

    Dim oCtrl As Control
    
    For Each oCtrl In ControlParent.Controls
        If TypeOf oCtrl Is MSForms.TextBox Then
            If oCtrl.TabIndex = TabIndex Then
                Set GetTextBoxByTabIndex = oCtrl
                Exit For
            End If
        End If
    Next oCtrl

End Function

Private Sub OKCommandButton_Click()

    With Sheets(11)

        .Unprotect Password:="iluvcl0udy"
        If Not AllmostEmpty(FirstNameTextBox) Then .Range("C9").Value = FirstNameTextBox.Value
                
        If LastNameTextBox <> "Optional" Then
            If Not AllmostEmpty(LastNameTextBox) Then .Range("D9").Value = LastNameTextBox.Value
        End If
        
        If Not AllmostEmpty(DOBTextBox) Then .Range("E9").Value = DOBTextBox.Value
        If Not AllmostEmpty(GenderComboBox) Then .Range("F9").Value = GenderComboBox.Value
        
        If CompanyTextBox <> "Optional" Then
        If Not AllmostEmpty(CompanyTextBox) Then .Range("G9").Value = CompanyTextBox.Value
        End If
        
        If Not AllmostEmpty(RDTextBox) Then .Range("C15").Value = RDTextBox.Value
        If Not AllmostEmpty(OptionComboBox) Then .Range("D15").Value = OptionComboBox.Value
        If Not AllmostEmpty(ProviderComboBox) Then .Range("E15").Value = ProviderComboBox.Value
        If Not AllmostEmpty(CPPTextBox) Then .Range("F15").Value = CPPTextBox.Value
        If Not AllmostEmpty(OASTextBox) Then .Range("G15").Value = OASTextBox.Value
        .Protect Password:="iluvcl0udy"
    End With
        
    With Sheets(11)

        .Unprotect Password:="iluvcl0udy"
        If Not AllmostEmpty(SFirstNameTextBox) Then .Range("C11").Value = SFirstNameTextBox.Value
        
        If SLastNameTextBox <> "Optional" Then
            If Not AllmostEmpty(SLastNameTextBox) Then .Range("D11").Value = SLastNameTextBox.Value
        End If
        
        If Not AllmostEmpty(SDOBTextBox) Then .Range("E11").Value = SDOBTextBox.Value
        If Not AllmostEmpty(SGenderComboBox) Then .Range("F11").Value = SGenderComboBox.Value
        
        If SCompanyTextBox <> "Optional" Then
        If Not AllmostEmpty(SCompanyTextBox) Then .Range("G11").Value = SCompanyTextBox.Value
        End If
        
        If Not AllmostEmpty(SRDTextBox) Then .Range("C17").Value = SRDTextBox.Value
        If Not AllmostEmpty(SOptionComboBox) Then .Range("D17").Value = SOptionComboBox.Value
        If Not AllmostEmpty(SProviderComboBox) Then .Range("E17").Value = SProviderComboBox.Value
        If Not AllmostEmpty(SCPPTextBox) Then .Range("F17").Value = SCPPTextBox.Value
        If Not AllmostEmpty(SOASTextBox) Then .Range("G17").Value = SOASTextBox.Value
        .Protect Password:="iluvcl0udy"
    End With
    
Unload Me
    
End Sub

Private Sub DOBTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With DOBTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub

Private Sub SDOBTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With SDOBTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub

Private Sub RDTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With RDTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub

Private Sub SRDTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With SRDTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub

Private Sub CPPTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With CPPTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub

Private Sub SCPPTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With SCPPTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub

Private Sub OASTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With OASTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub

Private Sub SOASTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With SOASTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "m/d/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub

Sub setupPlaceholder(txtBox As String, focus As Boolean)
    With Me.Controls(txtBox)
        If Len(.Text) = 0 And Not focus Then
            .Text = .Tag
            .ForeColor = vbGrayText
        ElseIf .Text = .Tag Then
            .Text = ""
            .ForeColor = vbWindowText
        End If
    End With
End Sub

Private Sub LastNameTextBox_Enter()
    setupPlaceholder LastNameTextBox.Name, True
End Sub

Private Sub LastNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder LastNameTextBox.Name, False
End Sub

Private Sub CompanyTextBox_Enter()
    setupPlaceholder CompanyTextBox.Name, True
End Sub

Private Sub CompanyTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder CompanyTextBox.Name, False
End Sub

Private Sub SLastNameTextBox_Enter()
    setupPlaceholder SLastNameTextBox.Name, True
End Sub

Private Sub SLastNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder SLastNameTextBox.Name, False
End Sub

Private Sub SCompanyTextBox_Enter()
    setupPlaceholder SCompanyTextBox.Name, True
End Sub

Private Sub SCompanyTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder SCompanyTextBox.Name, False
End Sub

Private Sub ProviderComboBox_Change()
    If ProviderComboBox.TextLength > 42 Then
    MsgBox "Please limit your input to 42 characters."
    ProviderComboBox.Text = Left(ProviderComboBox, ProviderComboBox.TextLength - 1)
    End If
End Sub

Private Sub SProviderComboBox_Change()
    If SProviderComboBox.TextLength > 42 Then
    MsgBox "Please limit your input to 42 characters."
    SProviderComboBox.Text = Left(SProviderComboBox, SProviderComboBox.TextLength - 1)
    End If
End Sub

Private Sub OptionComboBox_Change()
    If OptionComboBox.TextLength > 34 Then
    MsgBox "Please limit your input to 34 characters."
    OptionComboBox.Text = Left(OptionComboBox, OptionComboBox.TextLength - 1)
    End If
End Sub

Private Sub SOptionComboBox_Change()
    If SOptionComboBox.TextLength > 34 Then
    MsgBox "Please limit your input to 34 characters."
    SOptionComboBox.Text = Left(SOptionComboBox, SOptionComboBox.TextLength - 1)
    End If
End Sub
 
Upvote 0
@Mike would there be a better result by handling the date checking in a Class?
 
Upvote 0
diddi said:
@Mike would there be a better result by handling the date checking in a Class?
Click to expand...
This problem is the date handling per se, but triggering that handling. As far as classes, neither the Exit nor the AfterUpdate event are avaliabe to WithEvents Textboxes in Class modules.

@leopardhawk , AfterUpdate is one of the various events that you can code for a textbox.
Just change the declaration line for your current Exit event
VBA Code: 
Private Sub OASTextBox_AfterUpdate()
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

P
  • Question Question
If and ElseIf confusion !
Replies
5
Views
245
Micron
M
D
  • Solved
Set Focus Not Working
Replies
3
Views
103
Denny57
D
A
  • Question Question
calculation amounts with currency in textboxes on userform
Replies
2
Views
83
Abdo
A
P
  • Solved
Loading sheet row data to a userform issue.
Replies
7
Views
241
DanteAmor
DanteAmor
P
  • Question Question
Extract first and last name from email & Runtime error 13 type mismatch
Replies
2
Views
238
Alex Blakenburg
A

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top