Running VBA Macro based on drop down value in another sheet within workbook

[Nish12]

Hi I am able to successfully run the below VBA code (right click on Tab w/n excel worksheet and put this in View Code).

However, I'm running into issues when I try to run VBA code on the same worksheet tab based on the drop down value in another excel worksheet tab.

How do I modify the below code to work for me when referencing a cell value on another worksheet?

----------------------------------------------------------------------
Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("F61")) Is Nothing Then
Select Case Range("F61")
Case "(Select)": CurrentProductSpend_Hide
Case "Yes": CurrentProductSpend_Unhide
Case "No": CurrentProductSpend_Hide
End Select
End If
If Not Intersect(target, Range("F87")) Is Nothing Then
Select Case Range("F87")
Case "(Select)": IncrementalProductOpp_Hide
Case "Yes": IncrementalProductOpp_Unhide
Case "No": IncrementalProductOpp_Unhide
End Select
End If
End Sub
---------------------------------------------------------

Thanks!

[jdc2000]

For another sheet in the same workbook, you can specify which sheet the cell is on:

Sheet1.Cells(1, 1)

Possibly useful link:

http://www.informit.com/articles/art...21718&seqNum=5

[Zvoni]

First thing i learned: always fully qualify your ranges!
References to Ranges (Range, Cell etc.) without a qualifier references always to the Sheet-Object it's contained within.

As jdc said:
ThisWorkbook.Sheets("MyOtherSheetName").Range("F61")

[Nish12]

Thank you for your speedy replies!

Can you please tell me how I would go about modifying the below to incorporate the sheet name as both users have mentioned above?

I apologize I am new to VBA and took the below from another helpful post I found online so not sure what I need to modify..

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("F61")) Is Nothing Then
Select Case Range("F61")
Case "(Select)": CurrentProductSpend_Hide
Case "Yes": CurrentProductSpend_Unhide
Case "No": CurrentProductSpend_Hide
End Select
End If
End Sub

Thanks!

[jdc2000]

Maybe something like the following:

Private Sub worksheet_change(ByVal target As Range)

    If Not Intersect(target, Sheets("YourSheetNameHere").Range("F61")) Is Nothing Then
        Select Case Sheets("YourSheetNameHere").Range("F61")
            Case "(Select)": CurrentProductSpend_Hide
            Case "Yes": CurrentProductSpend_Unhide
            Case "No": CurrentProductSpend_Hide
        End Select
    End If

End Sub

[westconn1]

it seems to be a bit irrelevant as in a worksheet event the target can only be on the sheet containing the event, so any other sheet range could not intersect with target

if you want events to fire from any worksheet then you may need to use the workbook sheet change event, or your code should not be in the worksheet change event, but a general macro

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

i am not sure exactly what you are trying to do so this may not suit your needs

[Nish12]

This didn't seem to work

[Nish12]

it seems to be a bit irrelevant as in a worksheet event the target can only be on the sheet containing the event, so any other sheet range could not intersect with target

if you want events to fire from any worksheet then you may need to use the workbook sheet change event, or your code should not be in the worksheet change event, but a general macro

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

i am not sure exactly what you are trying to do so this may not suit your needs

I'm trying to have one general page, worksheet where a certain question is asked via drop down that has options, Yes or No

Based on the answer to the question to the general page, I want to then have it trigger whether another set of questions should appear via a macro on another sheet that is more product specific (basically if yes is selected on the main sheet, one set of questions would show up on another sheet, if no is answered then those questions remain hidden on the other sheet b/c they do not apply.

Hope this helps. Appreciate everyone's help.

Thanks!

[Nish12]

it seems to be a bit irrelevant as in a worksheet event the target can only be on the sheet containing the event, so any other sheet range could not intersect with target

if you want events to fire from any worksheet then you may need to use the workbook sheet change event, or your code should not be in the worksheet change event, but a general macro

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

i am not sure exactly what you are trying to do so this may not suit your needs

I'm trying to have one general page, worksheet where a certain question is asked via drop down that has options, Yes or No

Based on the answer to the question to the general page, I want to then have it trigger whether another set of questions should appear via a macro on another sheet that is more product specific (basically if yes is selected on the main sheet, one set of questions would show up on another sheet, if no is answered then those questions remain hidden on the other sheet b/c they do not apply.

Hope this helps. Appreciate everyone's help.

Thanks!

[Nish12]

Maybe something like the following:

Private Sub worksheet_change(ByVal target As Range)

    If Not Intersect(target, Sheets("YourSheetNameHere").Range("F61")) Is Nothing Then
        Select Case Sheets("YourSheetNameHere").Range("F61")
            Case "(Select)": CurrentProductSpend_Hide
            Case "Yes": CurrentProductSpend_Unhide
            Case "No": CurrentProductSpend_Hide
        End Select
    End If

End Sub

This didn't work

[westconn1]

is currentproductspend_hide etc procedures in another module? or what?

[Nish12]

is currentproductspend_hide etc procedures in another module? or what?

I'm including it on the View Code option on the specific sheet. How do I put it in a module that is over the entire workbook? If you can steer me to some information on how to do this I think this might help me resolve the issue.

Thanks!

[westconn1]

in the vba editor, try add a public module that is available throughout
right click the project > add module