dcsimg
Results 1 to 13 of 13

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

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

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

    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!

  2. #2
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,453

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

    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

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,916

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

    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")
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

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

    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!

  5. #5
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,453

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

    Maybe something like the following:

    Code:
    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
    Last edited by jdc2000; Feb 11th, 2019 at 04:39 PM.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,510

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

    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
    Code:
    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 do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

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

    This didn't seem to work

  8. #8

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

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

    Quote Originally Posted by westconn1 View Post
    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
    Code:
    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!

  9. #9

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

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

    Quote Originally Posted by westconn1 View Post
    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
    Code:
    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!

  10. #10

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

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

    Quote Originally Posted by jdc2000 View Post
    Maybe something like the following:

    Code:
    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

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,510

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

    is currentproductspend_hide etc procedures in another module? or what?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

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

    Quote Originally Posted by westconn1 View Post
    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!

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,510

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

    in the vba editor, try add a public module that is available throughout
    right click the project > add module
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width