Need help with VBA AfterUpdate in ComboBox

leopardhawk

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
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!
VBA Code: 
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
 
Last edited by a moderator:
Sort by date Sort by votes

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
88
Denny57
D
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
178
Denny57
D
D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
125
6StringJazzer
6StringJazzer
I
  • Question Question
Advice to alter current prolematic code on userform
Replies
9
Views
118
ipbr21054
I
P
  • Question Question
MsgBox Showing Twice
2
Replies
12
Views
300
dmt32
D

Forum statistics

Threads
1,224,527
Messages
6,179,334
Members
452,907
Latest member
Roland Deschain

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