Rerun a macro when a combobox value in a userform is changed

1
votes

I have a userform with a combobox to select the training type for various employee classes.

enter image description here

When the user selects one of the options from the dropdown menu it runs the macro below.

Private Sub TrainingType_Selection_Change()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Dim TrainingType_Selection As String

TrainingType_Selection = Hiring_Validation_Form.TrainingType_Selection.Value

If TrainingType_Selection = "Sustain - 30" Or TrainingType_Selection = "Sustain - 60" Then
    Hiring_Validation_Form.LinkingECPID_Selection.Visible = True
    Hiring_Validation_Form.LinkingECP_Label.Visible = True
End If

If TrainingType_Selection <> "New Hire" Then
    Hiring_Validation_Form.ReqReasonLv1_Selection.Visible = False
    Hiring_Validation_Form.Label6.Visible = False
    Hiring_Validation_Form.ReqReasonLv2_Selection.Visible = False
    Hiring_Validation_Form.Label11.Visible = False
End If

End Sub

The problem I'm running into is that if someone makes a selection from the drop-down menu and then changes their mind and selects another value from the drop-down menu it isn't re-running the macro. For example they change it from the above "New Hire" to "Sustain - 30". I have a clear button on the userform, but that clears the entire form which would not be ideal in a situation where the user only wants to change one input, not completely start over.

enter image description here

How do I get the TrainingType_Selection_Change() macro to re-run again when the combobox selection is changed

2
excelvbacomboboxuserform

2 Answers

0
votes

This line:

Application.ScreenUpdating = False

disables screen updates even after the macro has finished running.

Add

Application.ScreenUpdating = True

as the last line before End Sub to re-enable them.

0
votes

I was able to figure out a way to address this issue by using AfterUpdate() I added the below macro and it now updates the entire form when selections are changed.

Private Sub TrainingType_Selection_AfterUpdate()

    TrainingType_Selection = Hiring_Validation_Form.TrainingType_Selection.Value

    Hiring_Validation_Form.LinkingECPID_Selection.Visible = False
    Hiring_Validation_Form.LinkingECP_Label.Visible = False
    Hiring_Validation_Form.ReqReasonLv1_Selection.Visible = True
    Hiring_Validation_Form.Label6.Visible = True
    Hiring_Validation_Form.ReqReasonLv2_Selection.Visible = True
    Hiring_Validation_Form.Label11.Visible = True
    Hiring_Validation_Form.Label21.Visible = True
    Hiring_Validation_Form.NewHireSup_Selection.Visible = True

    Call TrainingType_Selection_Change

End Sub