How can I clear the previous values for a User Form Combo Box?

[USAMax]

I have a combo box dependent on the values of another combo box. It populates the values but when someone changes the values from another combo box I need to clear the values before adding values again.

Private Sub Eng1SD_Change()
    If EnableEvents Then                                    'Are Events Enabled?
        'EnableEvents = False                                'YES: Disable Events
        For Mon = DateValue(Eng1SD) To _
                  DateValue(Eng1SD) + 366 Step 7            '     Is the date a Monday between the Start Date and one year from the Start date?
            Eng1ED.AddItem Format(Mon, "mm/dd/yyyy", vbMonday) '  YES: Format the date and add it to the 1st Engineer's End Dates
            If Eng1ED = "" Then _
               Eng1ED = Format(Mon, "mm/dd/yyyy", vbMonday) '  Paste the formated date into the End Date
        Next Mon                                            '     ADVANCE TO THE NEXT MONDAY
        'EnableEvents = True                                 '     Enable Events again
    End If                                                  'END
End Sub

Private Sub Eng1ED_Change()
Dim SDR As Integer, TmpR As Integer                         'Available worksheet Start Date Row and Temp Row
Dim EDR As Integer                                          'Available worksheet End Date Row
Dim ALEC As Integer                                         'Available worksheet Lead Engineer Column
Dim AIMC As Integer                                         'Available worksheet Implementation Engineer Column
Dim APCC As Integer                                         'Available worksheet Preconfiguration Engineer Column
Dim APDC As Integer                                         'Available worksheet Predeployment Engineer Column
Const ADC As Integer = 1                                    'Available worksheet Date Column will always be the first column
Const ANC As Integer = 2                                    'Available worksheet Name Column will always be the second column
Dim Aws As Worksheet                                        'Create object for Available worksheet
    Set Aws = Sheets("Available")                           'Point the new object to the Available worksheet

    SDR = SoD(Aws, 1, DateValue(Eng1SD))                    'Get the first row the Start Date is found on
    EDR = EoD(Aws, 1, DateValue(Eng1SD))                    'Get the last row the End Date is found on
    ALEC = EngPos(Aws, 1, "LEAD")                           'Get Lead Engineer Column on Available worksheet
    AIMC = EngPos(Aws, 1, "IMPLEMENTATION")                 'Get IMPLEMENTATION Engineer Column on Available worksheet
    APCC = EngPos(Aws, 1, "PRECONFIG")                      'Get PRECONFIG Engineer Column on Available worksheet
    APDC = EngPos(Aws, 1, "PREDEPLOYMENT")                  'Get PREDEPLOYMENT Engineer Column on Available worksheet
    
    'LEED ENGINEER
    TmpR = SDR                                              'Get the Starting Date Row
    Do While TmpR <= EDR                                    'Is the Starting Date Row less than or equal to the End Date Row?
        If EngFreeTrghEndDate(Aws, Aws.Cells(TmpR, ALEC), _
           Eng1SD, Eng1ED, TmpR, ALEC) Then                 'YES: Is the Engineer Free Through the End Date?
            Eng1.AddItem Aws.Cells(TmpR, ALEC)              '     YES: Add the Lead Eng to the list.
        End If                                              '     END
        TmpR = TmpR + 1                                     '     Advance to the next row
    Loop                                                    'CHECK AGAIN
            
    'IMPLEMENTATION ENGINEER
    TmpR = SDR                                              'Get the Starting Date Row
    Eng1.AddItem "-- IMPLEMENTATION --"                     'Show that the following Engineers have been Implementation Engineers
    Do While TmpR <= EDR                                    'Is the Implementation Engineer not blank and does the Starting Date Row match the Starting Date?
        If EngFreeTrghEndDate(Aws, Aws.Cells(TmpR, AIMC), _
           Eng1SD, Eng1ED, TmpR, AIMC) Then                 'YES: Is the Engineer Free Through the End Date?
            Eng1.AddItem Aws.Cells(TmpR, AIMC)              '     YES: Add the Implementation Eng to the list.
        End If                                              '     END
        TmpR = TmpR + 1                                     '     Advance to the next row
    Loop                                                    'CHECK AGAIN
            
    'PRECONFIG ENGINEER
    TmpR = SDR                                              'Get the Starting Date Row
    Eng1.AddItem "-- PRECONFIG --"                          'Show that the following Engineers have been Preconfiguration Engineers
    Do While TmpR <= EDR                                    'Is the Preconfiguration Engineer not blank and does the Starting Date Row match the Starting Date?
        If EngFreeTrghEndDate(Aws, Aws.Cells(TmpR, APCC), _
           Eng1SD, Eng1ED, TmpR, APCC) Then                 'YES: Is the Engineer Free Through the End Date?
            Eng1.AddItem Aws.Cells(TmpR, APCC)              'YES: Add the Preconfig Engineer to the list.
        End If                                              '     END
        TmpR = TmpR + 1                                     '     Advance to the next row
    Loop                                                    'CHECK AGAIN
            
        'PREDEPLOYMENT ENGINEER
    TmpR = SDR                                              'Get the Starting Date Row
    Eng1.AddItem "-- PREDEPLOYMENT --"                      'Show that the following Engineers have been Predeployment Engineers
    Do While TmpR <= EDR                                    'Is the Predeployment Engineer not blank and does the Starting Date Row match the Starting Date?
        If EngFreeTrghEndDate(Aws, Aws.Cells(TmpR, APDC), _
           Eng1SD, Eng1ED, TmpR, APDC) Then                 'YES: Is the Engineer Free Through the End Date?
            Eng1.AddItem Aws.Cells(TmpR, APDC)              '     YES: Add the Preconfiguration Engineer to the list.
        End If                                              '     END
        TmpR = TmpR + 1                                     '     Advance to the next row
    Loop                                                    'CHECK AGAIN
        
    EnableEvents = False
    Eng1 = ""
    EnableEvents = True
End Sub

Thank you,

 

[shg]

Do we really need to dig through all of that to answer the question, or could you provide an example that cuts to the nub?

 

[USAMax]

I agree with you but I had a couple of people ask for more in the past...

This is what I am using to populate the combo box Eng1.

            Eng1.AddItem Aws.Cells(TmpR, ALEC)              '     YES: Add the Lead Eng to the list.

What can I do to clear it at the beginning of the Sub?

 

[Rick Rothstein]

I agree with you but I had a couple of people ask for more in the past...

This is what I am using to populate the combo box Eng1.

            Eng1.AddItem Aws.Cells(TmpR, ALEC)              '     YES: Add the Lead Eng to the list.

What can I do to clear it at the beginning of the Sub?

The ActiveX ComboBox on a UserForm has a Clear method that will do what you asked for, simply execute this...

Eng1.Clear

Where I assu Eng1 is the name of your ComboBox.

 

[USAMax]

Thank you Rick,

That's clear! I tried ClearContent and something else but I didn't think to try clear.

Thank you again,