Trigger subroutine via combo box

**Ivydesert** · 05-24-2011, 09:46 AM

Is there is any way to trigger a subroutine when I click on a value inside a combo box?

Basically, I have a subroutine set up to generate a column of unique values based on whatever value is in the cell linked to a combo box, but the only way I have gotten it to run is through the Worksheet_Change event, as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("C3:C6")        'Linked cells from combo boxes
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then
        Call DateRange                   '-----------------------------------------
        Call BegYearRange                'Subroutines to be called when
        Call EndYearRange                'combo box values change
        Call MonthOffsetRange            '-----------------------------------------
    End If
    Target.Select                        'Brings focus back to original selection
End Sub

The values update when I press enter after manually editing a cell, but I would like for this update to be automatic, if possible. Is there any way to do this?

**tigeravatar** · 05-24-2011, 02:55 PM

Ivydesert,

I have attached a sample workbook that uses an OLE Object (ActiveX control) combobox, a Form control combobox, and a Validation list in a cell. Module1 contains code for the Form control, and Sheet1 contains code for the OLE Object and the validation list. The code runs when a change in a drop-down list is made. Just replace the msgboxes with your desired code once you've chosen which one you want to do.

Hope that helps,
~tigeravatar