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:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Why not change the property of the Combo, so that it needs an exact match?
 
Upvote 0
@Fluff as always, thanks for stepping in, I value your input a lot. The way the code is now, it does have to be an exact match, the Combobox will only accept M, F, or a blank (which is what I want). I actually like it that the user can type in an M or an F as well as choose it from the dropdown. I also like that it changes to red (as described above) if they enter any other character. I would just love for it to go from red back to normal if they correct their mistake. I'm pretty sure there has to be a way to do it. Thanks!
 
Upvote 0
But if it's set to exact match (1-fmMatchEntryComplete) then they cannot enter anything other than M, F or leave blank
 
Upvote 0
You can set it in the properties window of the combo, or you can use this in the userform initialise
VBA Code: 
Me.SGenderComboBox.MatchEntry=fmMatchEntryComplete
 
Upvote 0
@Fluff I checked MatchEntry in the properties of both ComboBoxes and they are already set to 'complete'. I still am hoping that someone will help me do what I would like to do as described in Post#1. Anyone??
 
Upvote 0
leopardhawk said:
I checked MatchEntry in the properties of both ComboBoxes and they are already set to 'complete'.
Click to expand...
In that case there is no way anyone can enter anything other than "M" or "F" and leave the combo, so it would never be coloured red.
 
Upvote 0
Hmmm, and yet they both allow me to enter any character but are deleted as soon as I tab away. I guess that's what the 'complete' part does?.
 
Upvote 0
I cannot leave the combo if the wrong value is entered, but you can try
VBA Code: 
Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.ComboBox1
      If .Text = "M" Or .Text = "F" Or .Text = "" Then
         .BackColor = rgbWhite
         Label4.Caption = ""
         Label4.ForeColor = rgbBlack
      Else
         Label4.Caption = "Please select M or F"
         Label4.ForeColor = RGB(255, 55, 55)
         .BackColor = RGB(255, 55, 55)
      End If
   End With
End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

P
  • Question
Exiting a Textbox Problem
2
Replies
10
Views
167
Pacman52
P
Noid67
  • Question
Multiple worksheet change and Formula use
2
Replies
17
Views
322
mumps
mumps
P
  • Solved
Advice needed on where to put code that stops duplicate entries
Replies
5
Views
299
Pacman52
P
countryfan_nt
  • Solved
Placing chosen result from a userfrom on a specific cell.
Replies
1
Views
173
Micron
M
P
  • Question
Check two different statements on the Exit Routine
Replies
3
Views
218
Micron
M

Forum statistics

Threads
1,214,590
Messages
6,120,421
Members
448,961
Latest member
nzskater

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