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.
Thank you,
Code:
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,