CODEWITHSUNDEEP
vba
Alien_Explorer
Alien_Explorer

Reputation: 867

VBA "Select Case" not working on the other worksheet

So my problem is: I use a specific code on one worksheet (Sheet1):

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Range("B24")

    Case "Standard"
        Sheets("Sheet2").Visible = False
        Rows("29:47").EntireRow.Hidden = False

    Case "Medium"
        Sheets("Sheet2").Visible = True
        Rows("29:47").EntireRow.Hidden = True

    Case "High"
        Sheets("Sheet2").Visible = True
        Rows("29:47").EntireRow.Hidden = True

    Case Else
        Sheets("Sheet2").Visible = False
        Rows("29:47").EntireRow.Hidden = True

    End Select

End Sub

Once the Sheet2 becomes visible (based on the above), I have this code added to Sheet2 specifically:

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Range("B14")

    Case "Medium"
        Rows("6:12").EntireRow.Hidden = True

    Case Else
        Rows("6:12").EntireRow.Hidden = False

    End Select

End Sub

The problem is that the cell (Sheet2) B14 consisting of =Sheet1!B24 does not interact with the code on Sheet2 and thus doesn't hide/unhide rows. I need to physically select =Sheet1!B24 and click Enter in it and then the rows are hidden/unhidden. Please advise why it is not happening automatically.

PS. Calculation Ops are set to Auto

Thanks

Upvotes: 0

Views: 220

Answers (1)

SierraOscar
SierraOscar

Reputation: 17647

Formulas don't trigger a "change" event. Use the Worksheet_Calculate event instead:

Private Sub Worksheet_Calculate()

    Application.EnableEvents = False

    Select Case Range("B24")

    Case "Standard"
        Sheets("Sheet2").Visible = False
        Rows("29:47").EntireRow.Hidden = False

    Case "Medium", "High"
        Sheets("Sheet2").Visible = True
        Rows("29:47").EntireRow.Hidden = True

    Case Else
        Sheets("Sheet2").Visible = False
        Rows("29:47").EntireRow.Hidden = True

    End Select

    Application.EnableEvents = True

End Sub

Important: Note the disabling of events before the code - this is to prevent an infinite loop.

Upvotes: 1

Related Questions