none
New to this - How to set Keycell Range to a cell in different worksheet RRS feed

  • Question

  • All,

    I am very new to VBA, but, have decided to try and sharpen my skills with it to further Excel's usefulness in my current job.

    I would like to change my KeyCells to reference a cell on another worksheet within the same workbook. Currently, the code is written to reference cell "C4" on 'Sheet1(Market)'. If C4 = Yes, then Rows 75,78,107-110 will be hidden and Row 73 will be visible. If cell "C4" = No, then Rows 75,78,107-110 will be visible and Row 73 will be hidden. If cell "C4" = Select From Dropdown, all above listed Rows will be hidden.

    I would like to change my KeyCells to 'Sheet6 (III-Model Type & Risk Rank)' Cell 'D24' so that if it is changed between 'Yes', 'No' and 'Select From Dropdown' that it will hide the rows on 'Sheet1(Market)'. Is this possible?

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("C4")
    'Market
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
            ' run code when one of the designated cells has been changed.
            ' Place your code here.
        If KeyCells.Value = "Yes" Then
            Rows("73").EntireRow.Hidden = True
            Rows("75").EntireRow.Hidden = False
            Rows("78").EntireRow.Hidden = False
            Rows("107").EntireRow.Hidden = False
            Rows("108").EntireRow.Hidden = False
            Rows("109").EntireRow.Hidden = False
            Rows("110").EntireRow.Hidden = False
        End If
        If KeyCells.Value = "No" Then
            Rows("73").EntireRow.Hidden = False
            Rows("75").EntireRow.Hidden = True
            Rows("78").EntireRow.Hidden = True
            Rows("107").EntireRow.Hidden = True
            Rows("108").EntireRow.Hidden = True
            Rows("109").EntireRow.Hidden = True
            Rows("110").EntireRow.Hidden = True
        End If
        If KeyCells.Value = "Select From Dropdown" Then
            Rows("73").EntireRow.Hidden = False
            Rows("75").EntireRow.Hidden = False
            Rows("78").EntireRow.Hidden = False
            Rows("107").EntireRow.Hidden = False
            Rows("108").EntireRow.Hidden = False
            Rows("109").EntireRow.Hidden = False
            Rows("110").EntireRow.Hidden = False
        End If
    End If

    Monday, May 7, 2018 3:17 PM

All replies

  • Assuming that the name of the sheet is III-Model Type & Risk Rank, you can use

    Set KeyCells = Worksheets("III-Model Type & Risk Rank").Range("D24")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, May 7, 2018 4:36 PM
  • Thank you, Hans. When I insert the changes you listed above with the rest of the code being the same, I get a runtime error. I would include a screenshot, however, my account has not been verified yet. Here is the code after I click debug with the line bolded, italicized and underlined:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Worksheets("II-Model Type & Risk Rank").Range("D24")
    'Market
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
            ' run code when one of the designated cells has been changed.
            ' Place your code here.
        If KeyCells.Value = "Quantitative" Then
            Rows("73").EntireRow.Hidden = True
            Rows("75").EntireRow.Hidden = False
            Rows("78").EntireRow.Hidden = False
            Rows("107").EntireRow.Hidden = False
            Rows("108").EntireRow.Hidden = False
            Rows("109").EntireRow.Hidden = False
            Rows("110").EntireRow.Hidden = False
        End If
        If KeyCells.Value = "Qualitative" Then
            Rows("73").EntireRow.Hidden = False
            Rows("75").EntireRow.Hidden = True
            Rows("78").EntireRow.Hidden = True
            Rows("107").EntireRow.Hidden = True
            Rows("108").EntireRow.Hidden = True
            Rows("109").EntireRow.Hidden = True
            Rows("110").EntireRow.Hidden = True
        End If
        If KeyCells.Value = "Select From Dropdown" Then
            Rows("73").EntireRow.Hidden = False
            Rows("75").EntireRow.Hidden = False
            Rows("78").EntireRow.Hidden = False
            Rows("107").EntireRow.Hidden = False
            Rows("108").EntireRow.Hidden = False
            Rows("109").EntireRow.Hidden = False
            Rows("110").EntireRow.Hidden = False
        End If
    End If

    Monday, May 7, 2018 4:55 PM
  • You cannot intersect a range on another sheet with Target, which is always a range on the active sheet.

    By the way, Range(Target.Address) is exactly the same as Target, only more convoluted.

    You will have to use the Worksheet_Change event procedure of the II-Model Type & Risk Rank sheet, NOT the Worksheet_Change event procedure of the Market sheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim KeyCells As Range
        Dim WS As Worksheet

        ' The variable KeyCells contains the cell that will cause an alert when it is changed.
        Set KeyCells = Range("D24")
        ' The variable WS refers to the worksheet on which we want to show/hide rows
        Set WS = Worksheets("Market")
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
            Select Case KeyCells.Value
                Case "Yes"
                    WS.Rows("73").EntireRow.Hidden = True
                    WS.Rows("75").EntireRow.Hidden = False
                    WS.Rows("78").EntireRow.Hidden = False
                    WS.Rows("107:110").EntireRow.Hidden = False
                Case "No"
                    WS.Rows("73").EntireRow.Hidden = False
                    WS.Rows("75").EntireRow.Hidden = True
                    WS.Rows("78").EntireRow.Hidden = True
                    WS.Rows("107:110").EntireRow.Hidden = True
                Case "Select From Dropdown"
                    WS.Rows("73").EntireRow.Hidden = False
                    WS.Rows("75").EntireRow.Hidden = False
                    WS.Rows("78").EntireRow.Hidden = False
                    WS.Rows("107:110").EntireRow.Hidden = False
            End Select
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, May 7, 2018 8:51 PM