click event not working

[Noz2k]

I have a combobox in a worksheet which when the user changes the value in the box works perfectly (it shows the list of values in column A on sheet 1). However when the user just clicks on the dropdown tab it doesn't update the list.

The code on both the click event and the change event is the same. Any idea why this would not work and how to solve it?

The code is as follows:

Private Sub cboReport_Click()
Sheets("Sheet1").UnProtect "abc123"
Dim cl As Range
Dim lngLastRow As Long
    lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
 
    If lngLastRow <> 3 Then
        cboReport.List = Worksheets("Sheet1").Range("A3:A" & lngLastRow).Value
    Else
        cboReport.List = Worksheets("Sheet1").Range("A3:A4").Value
    End If
End Sub

 

[Noz2k]

Any idea why the list would update when the value is changed, but not when the dropdown arrow is clicked?

 

[GTO]

Hi there,

See VBA help for the Click event. In short, clicking on a combobox or the box's drop-down does not fire Click. If you select a value using the drop-down, the Click event is called, but only after the Change event has already run. The Change event is called regardless of whether the value is changed by typing in the box or selecting from the drop-down.

 

[Noz2k]

Ah, I thought this might be the case.

Originally I had the code running on the activate event of the sheet, but that then caused a problem when the sheet was saved in a new workbook as it references sheet1 which doesn't exist in the new workbook.

What combobox event can I apply the code to so that it runs when the dropdown arrow is clicked?

 

[Noz2k]

not to worry just found the dropdown button click which I didn't know existed.

Am still pretty inexperienced in Vba, and that just goes to show it.

 

[GTO]

Glad you found it. Just for kicks, in a new/blank wb, you could make a userform with a combobox and a couple of other controls (just to see that we're taking focus away). In the form's module:

Private Sub ComboBox1_Change()
    Debug.Print "Changed"
End Sub
    
Private Sub ComboBox1_Click()
    Debug.Print "Clicked"
End Sub
    
Private Sub ComboBox1_DropButt*******()
    Debug.Print "DropDownClicked"
End Sub
    
Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .AddItem 1
        .AddItem 24
        .AddItem 23
        .AddItem 3
        .AddItem 4
    End With
End Sub

If you reduce vbide and excel's windows to half screen ea, you can run the form and watch what gets fired and the order, in the immediete window.

 

[RoryA]

Originally I had the code running on the activate event of the sheet, but that then caused a problem when the sheet was saved in a new workbook as it references sheet1 which doesn't exist in the new workbook.

So does your Click code, so how does that still work? (or doesn't it?)