Using VBA to execute Scenarios Based On Combo Box Selection on an Inactive Worksheet

[Zwen]

Hi, I am new to VBA so please excuse this question if it appears to be elementary. I am working on a model and am trying to write a code in VBA that allows me to operate scenarios embedded in Scenario Manager on an active worksheet from an inactive worksheet.

I have managed to write a code that runs the scenarios from a combo box actually on the active worksheet using 'Worksheet' 'Change' in VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$16" Then
ActiveSheet.Scenarios(Target.Value).Show
End If

But am struggling to write a code that will allow me to operate the same scenarios from an alternative worksheet within the same work book. The ActiveWorksheet is called 'NGN Inputs' FYI and is "Sheet 14" and the Inactive worksheet (i.e. the one not containing the scenarios) is called 'Assumptions' ("Sheet4")

Again this is probably fairly elementary but I am new to VBA and would appreciate any help.

Many thanks.

 

[crimson_b1ade]

I think if you reference the sheet you want to change (rather than ActiveSheet) it should work (hopefully):

If Target.Address = "$O$16" Then Sheets("NGN Inputs").Scenarios(Target.Text).Show

 

[Zwen]

Perfect. That worked a treat. Many thanks for the assistance. Much appreciated.