Enable Command Buttons based on Combobox

Question

I have a userform that needs to display different options based on the user that is opening the form. Some of these options should only be enabled once a selection has been made in a combobox, but I can't seem to find a way to get them to update after a combobox selection is chosen. What I'm using is:

Private Sub cbCharts_AfterUpdate()
If Me.cbCharts Is "" Then
    Me.bQuickEntry.Enabled = False
    Me.bView.Enabled = False
    Exit Sub
ElseIf UserDep = "Quality Control" Then
    Me.bQuickEntry.Enabled = True
    Me.bView.Enabled = True
    Me.bAdjust.Enabled = True
Else
    Me.bView.Enabled = True
    Me.bQuickEntry.Enabled = True
End If
End Sub

Where cbCharts is the combobox in question, and bQuickentry, bView and bAdjust are the buttons. Using this code, the buttons don't enable until I click somewhere else on the form, instead of immediately after making the selection. Then, if I clear the combobox Excel hangs and has to be force closed.

I've tried instead using Private Sub cbCharts_OnExit with the same code, but it doesn't do anything at all.

I know I can just leave all the buttons enabled and visible add a verification step to the code for each button to ensure that there is a valid combobox selection before proceeding, but I would prefer to enable and set their visibility to prevent user confusion, as some of these buttons will not be usable by the majority of spreadsheet users.

What am I doing wrong?

Brian Pressler Brian Pressler · Accepted Answer · 2015-10-15T23:04:18

Try putting your code into the cbCharts_Click() event function instead. This event is fired as soon as the user selects an item in the list.

Enable Command Buttons based on Combobox

1 Answers