VBA for beginners!

Copper Contributor

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 :)

8 Replies

@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

 

@Hans Vogelaar 


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

@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.

@Hans Vogelaar
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?

@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.

@Hans Vogelaar 

 

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

@North_Yorks 

You told use to look for "No", but the drop-downs actually have values "YES" and "NO". That matters!

See the attached version.

@Hans Vogelaar
Thank you - and sorry I thought I'd mentioned the yes & no thing!