Forum Discussion

Copper Contributor

Mar 23, 2022

VBA for beginners!

Hi, I have used a similar VBA in a different spreadsheet and have tried to copy it over but it wont work 😞 I have a single sheet workbook with lots yes/no questions in different sections. If you an...

MVP

Mar 23, 2022

North_Yorks

This is not a macro, but an event procedure. It belongs in the worksheet module of the relevant worksheet, not in a standard module.

And you should use Worksheet_Change, not Worksheet_SelectionChange.

I'd write it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Select Case Target.Address(False, False)
        Case "E10"
            Rows("11:56").EntireRow.Hidden = (Target.Value = "No")
        Case "E12"
            Rows("13:17").EntireRow.Hidden = (Target.Value = "No")
        Case "E19"
            Rows("20:29").EntireRow.Hidden = (Target.Value = "No")
        Case "E31"
            Rows("32:37").EntireRow.Hidden = (Target.Value = "No")
        Case "E39"
            Rows("40:52").EntireRow.Hidden = (Target.Value = "No")
        Case "E54"
            Rows("55:56").EntireRow.Hidden = (Target.Value = "No")
        Case "E58"
            Rows("59:62").EntireRow.Hidden = (Target.Value = "No")
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

North_Yorks

Copper Contributor

Mar 23, 2022

HansVogelaar

Thank you for your quick response i have added this in to the Visual Basic and saved the workbook as an xlsm but it still doesn't work none of the rows hide whatever option is selected.
Also it I go to run i get the Macro name pop up - see attached. I've never had this before

Doc1.docx134 KB

HansVogelaar
MVP
Mar 23, 2022
North_Yorks

As I mentioned in my previous reply: it is not a macro; you cannot run it.

Excel will run it automatically for you whenever you change the value of one of the cells E10, E12, E19, E31 etc.

Try entering Yes or No in one of those cells.
- North_Yorks
  Copper Contributor
  Mar 23, 2022
  HansVogelaar
  I don't know if it affects the instructions but I have drop down lists to select the yes/no answers. I have tried it and it's not working. Does it still need to be saved as a xlsm?
  - HansVogelaar
    MVP
    Mar 23, 2022
    North_Yorks
    
    Yes, the workbook should be saved as a macro-enabled workbook (*.xlsm).
    
    I have attached a small sample workbook. Try selecting No from the dropdown in one of the dark green cells.
    HideRows.xlsm16 KB