Mar 23 2022 04:24 AM
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 answer yes then you need to complete the rest of the questions in that section, if you answer no then it hides that section so you can jump to the next.
I'm sure when I set up the first Macro I didn't need to save it as a name either - has office 365 changed the way we create them?
These are the instructions I've been using:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("E10") = "No" Then
Rows("11:56").EntireRow.Hidden = True
Exit Sub
Else
Rows("11:56").EntireRow.Hidden = False
End If
If Range("E12") = "No" Then
Rows("13:17").EntireRow.Hidden = True
Else
Rows("13:17").EntireRow.Hidden = False
End If
If Range("E19") = "No" Then
Rows("20:29").EntireRow.Hidden = True
Else
Rows("20:29").EntireRow.Hidden = False
End If
If Range("E31") = "No" Then
Rows("32:37").EntireRow.Hidden = True
Else
Rows("32:37").EntireRow.Hidden = False
End If
If Range("E39") = "No" Then
Rows("40:52").EntireRow.Hidden = True
Else
Rows("40:52").EntireRow.Hidden = False
End If
If Range("E54") = "No" Then
Rows("55:56").EntireRow.Hidden = True
Else
Rows("55:56").EntireRow.Hidden = False
End If
If Range("E58") = "No" Then
Rows("59:62").EntireRow.Hidden = True
Else
Rows("59:62").EntireRow.Hidden = False
End If
End Sub
Any suggestions would be greatly appreciated :)
Mar 23 2022 05:00 AM - edited Mar 23 2022 05:01 AM
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
Mar 23 2022 05:28 AM
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
Mar 23 2022 05:33 AM
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.
Mar 23 2022 06:32 AM
Mar 23 2022 06:42 AM
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.
Mar 23 2022 07:08 AM
I have the same instruction but mine still won't work - I have removed sensitive material but please look at a basic version of mine
Mar 23 2022 07:27 AM
You told use to look for "No", but the drop-downs actually have values "YES" and "NO". That matters!
See the attached version.
Mar 25 2022 04:34 AM