Run a VBA code from changing the value of a cell in another spreadsheet with a combo box

Question

Saw this question was never completely answered in similar posts so I'll try again. Starting from a blank workbook I would like to have a drop down list on "Sheet1" but with the input range on "Sheet2!$A$1:$A$4" and the Cell Link on "Sheet2!$H$5"

When a user selects an option from the dropdown list in Sheet1, the value in Sheet2 should change and that should start the macro. However, I was only able to get a macro to work if Sheet2 was the active sheet. Is there a code that can execute if it detects changes in a different sheet? Question 2, do I put the code in "ThisWorkbook" or "Sheet1"?

Keypoints: Macro caused by a change in a cell in a non-active worksheet.

Thanks! Art

You need to put the code in the Sheet which would start the macro if there is any changes in the target range. See this for more info — Stupid_Intern
Worksheets("sheet2"). Range("a1") is how you work with different sheets — Nathan_Sav

user3598756 user3598756 · Accepted Answer · 2016-02-16T10:11:37

in my excel 2013 the linked cell updating doesn't fire any worksheet or workbook event, not even the "Worksheet_Change" one

so I would workaround it by means of the Worksheet_Calculate" event and some additional "tricks" like follows

the event handler is:

Private Sub Worksheet_Calculate()

    If Me.Range("controlCell") <> Me.Range("linkedCell") Then
        Application.EnableEvents = False ' to prevent following instruction from firing this event

        Me.Range("controlCell") = Me.Range("linkedCell") ' save the current value to be used for future checkings
        Call mySub ' <--- place here your actual sub name along with proper arguments

        Application.EnableEvents = True ' to prevent following instruction from firing this event
    End If

End Sub

to be placed in "Sheet2" and which in turn requires the following additional "tricks":

name the linked cell ("Sheet2!$H$5") after "linkedCell" (or whatever you want but be consistent with it in subsequent steps and in the event code above);
choose a cell in Sheet2 where to put a simple "echo" (i.e. formula =linkedCell) of the linked cell
choose a cell in Sheet2 and name it after "controlCell" (or whatever you want but be consistent with it...)

that way it should work like follows

you select a value in the DropDown list in "Sheet1"
that will update "linkedCell" and consequently the "echo" cell
the latter will finally fire the Worksheet_Calculate event of Sheet2, which checks the "linkedCell" against the "controlCell" and, if different, runs your sub and updates "controlCell" for following use

Run a VBA code from changing the value of a cell in another spreadsheet with a combo box

1 Answers