Combobox linked cell value change does not fire worksheet change event

2
votes

As per the question: selecting a new value in an ActiveX combobox linked to a cell by it's .LinkedCell property does not fire the worksheet change event.

I know there are various events for combo boxes like it's own change event but none of them are really suitable for what I need to do (custom data validation on the changed cell).

As a workaround I pass the .LinkedCell to my validation code on the combobox mouse up and key up events (I can't use lost focus... long story, and on change fires on every character change which is too much).

Does anyone know of a cleaner way to pass the value around at the event level once the user has finished using the control?

1
vbaexceleventscombobox

1 Answers

1
votes

If you want to trigger the ChangeEvent in a worksheet, without doing anything, here is a way to do it:

In a module:

Option Explicit

Public Sub CheckMe()
    Application.Run "tblDB.Worksheet_Change", tblDB.Cells(1, 1)
End Sub

In a workbook, named tblDB:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print "triggered"
End Sub

Now, whenever you run the CheckMe, the Worksheet_Change event would be triggered.