Share via

Excel VBA Code Not Running with No Error

Anonymous
2023-12-19T21:59:02+00:00

Hi, I am trying to utilize a VB code in Excel that will hide some rows depending on the value of another cell. In my case, I have a data validation list in cell F42 that gives the user the option of selecting either "Yes" or "No". If the user selects "No", I want to hide rows 43 thru 45. I have followed this video as a guide, changing only my row numbers and target range. My code is copied below.

The issue that I'm running into is that it doesn't seem like Excel recognizes that that code exists at all. Nothing happens in the spreadsheet when I toggle between "Yes" or "No" in cell F42, and when I click the "Macro" button in the developer tab, it comes up completely empty - like it doesn't even recognize that there is a macro in the worksheet.

Private Sub HideRows(ByVal Target As Range)

ActiveSheet.Activate
If Not Application.Intersect(Range("F42"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "No": Rows("43:45").EntireRow.Hidden = True
    Case Is = "Yes": Rows("43:45").EntireRow.Hidden = False
    End Select
End If

End Sub
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-12-19T22:27:17+00:00

    The code is not a macro (it is Private, it has an argument Target and it is not stored in a standard module but in the worksheet module.

    You change the name of the procedure; that won't work in this situation. It must be named Worksheet_Change instead of HideRows.

    Remarks:

    • ActiveSheet.Activate is superfluous - delete that line.
    • Range(Target.Address) is needlessly long - just use Target.
    • The code can be shortened to Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("F42"), Target) Is Nothing Then Rows("43:45").EntireRow.Hidden = (Range("F42").Value = "No") End If End Sub

    1 person found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest