Forum Discussion
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...
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
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 beforeAs 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.
- 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?
