1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by Zwen, Jul 22, 2011.

Thread Status:
Not open for further replies.
  1. Zwen

    Zwen Thread Starter

    Joined:
    Jul 22, 2011
    Messages:
    2
    Hi, I am new to VBA so please excuse this question if it appears to be elementary. I am trying to write a code in VBA that allows me to operate scenarios 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':

    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.
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    don't work with Activesheet, set up your procedure like this...

    Code:
    dim wb as workbook
    dim WS1, WS2 as worksheet
    
    Set WB = Thisworkbook
    
    Set WS1 = WB.sheets("NGN Inputs")
    Set WS2 = WB.sheets("Assumptions")
    then you can refer to your cells like this..

    WS1.cells(16,15)


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$O$16" Then
    WS1.Scenarios(Target.Value).Show
    End If
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1008703

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice