Need help with VBA AfterUpdate in ComboBox

[leopardhawk]

Hello forum friends,

I have this wonderful piece of code here that works just fine except for one thing that bothers me aesthetically. When the user clicks on the Personal Info command button on the worksheet, they will see a UserForm pop-up with various fields to fill in and then hit the OK button. One of the fields is a ComboBox (GenderComboBox) where they choose their gender, either M or F. The code will change the backcolor and the caption to red if they enter anything other than an M or an F. Like I said, this all works great. The problem I am trying to solve is: for example, they enter a Z, the backcolor and the caption change to red, the user corrects their mistake to an M and when they then tab to the next field, the gender backcolor and caption are still red!

I would like the caption and the backcolor to return to what they were before the change to red. I tried to work in an AfterUpdate event but when I did that, the backcolor and the caption for the gender ComboBox never change to red when an error is made. I so wanted to figure this out on my own and yet here I am, asking for help again! Any ideas or suggestions are much appreciated.

I'm attaching the code for the whole UserForm but the relevant part is right near the top. Also, note that there is another ComboBox called SGenderComboBox. If I can get a solution for the first one then I can apply that same code to the second combobox. Thanks!

Private Sub CloseCommandButton_Click()

Unload Me

End Sub

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

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

Private Sub OKCommandButton_Click()

    With Sheets(11)

        .Unprotect Password:="passwordhere"
        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:="passwordhere"
    End With
       
    With Sheets(11)

        .Unprotect Password:="passwordhere"
        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:="passwordhere"
    End With
   
Unload Me
   
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
       
    GenderComboBox.AddItem "M"
    GenderComboBox.AddItem "F"
       
    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 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

 

[Norie]

Why not use the Change event instead of BeforeUpdate?

 

[leopardhawk]

@Norie I watched a video where the instructor recommended the BeforeUpdate event. I'm assuming you saw Post#1. Everything works great but I want the backcolor to return to white and the label to return to what it was if the user corrects their mistake. I tried the AfterUpdate and then the comboxbox never changes to red. Any ideas?

 

[Norie]

Have you tried using Change?

 

[Fluff]

Did you see post#10?

 

[leopardhawk]

@Fluff hi Fluff, yes, I saw post#10 and I tried to incorporate your code into mine and it's basically doing the same thing. Perhaps it's because I'm flapping in the wind here trying to figure this out. I'm not as bright as a lot of the folks on this forum, really just above beginner level and trying hard to understand a lot of what I see. I would like to keep my code 'as is' because it's working but just make a modification that will do what I asked in Post#1.

@Norie hello, no, I did not try using a Change. What would that look like and could it potentially solve my dilemma?

 

[Fluff]

I would like to keep my code 'as is' because it's working but just make a modification that will do what I asked in Post#1.

That's what the code in post#10, you just need to change the name of your combobox.

 

[Norie]

The change event might look something like this.

Private Sub GenderComboBox_Change()

   With Me.GenderComboBox
      If .ListIndex <> -1 Then
         .BackColor = rgbWhite
         Me.Label4.Caption = ""
         Me.Label4.ForeColor = rgbBlack
      Else
        .BackColor = RGB(255, 55, 55)
         Me.Label4.Caption = "Please select M or F"
         Me.Label4.ForeColor = RGB(255, 55, 55)
      End If
   End With
   
End Sub

 

[leopardhawk]

@Norie Hi Norie, I think your change event is close, it's definitely changing the backcolor and the label to remind the user to choose M or F but it will still send whatever character is in the combobox to the worksheet even if it's not an M or an F. Is there a way to prevent them from using the OK button until there is an M or an F in the combobox?

 

[Norie]

In whichever sub/event that is intended to commit data check the ListIndex property of the relevant combobox.

If it's -1 then tell them they haven't entered/selected a suitable value from the list in the combobox and exit the sub/event.

 

[leopardhawk]

@Fluff Thank you for your help, again! I appreciate your persistence in getting through to me. I used your code in post#10 with a couple of mods and now it is working exactly how I wanted it to. My working code below if you want to see what I changed. Thanks again!

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