Forum Discussion

North_Yorks's avatar
North_Yorks
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 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 🙂

excel
Macros and VBA
office 365

8 Replies

  • HansVogelaar's avatar
    HansVogelaar
    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's avatar
      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

      • HansVogelaar's avatar
        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.

Resources