Forum Discussion

North_Yorks's avatar
North_Yorks
Copper Contributor
Nov 25, 2020

VBA Formula

Hi, I have a spreadsheet that uses VBA to hide rows upon opening. I would like the workbook to open on the first tab. I have seen the formula to use for this:

Private Sub Workbook_Open()
 
Worksheets("Example").Activate
 
End Sub
 
However as my workbook page already uses a formula to collapse / hide rows in other sheet upon opening I'm not sure where to fit this in to my set of instructions.
These are the instruction I have and I have put the new one at the top but it doesn't work. Please help!
 

Private Sub Workbook_Open()

Worksheets("General Instructions").Activate

Sheets("Americas").Activate
Rows("8:18").EntireRow.Hidden = True
Rows("21:30").EntireRow.Hidden = True
Rows("33:42").EntireRow.Hidden = True
Rows("45:49").EntireRow.Hidden = True

Sheets("ANZASAF").Activate
Rows("8:18").EntireRow.Hidden = True
Rows("21:24").EntireRow.Hidden = True
Rows("27:36").EntireRow.Hidden = True
Rows("39:45").EntireRow.Hidden = True

Sheets("CEU").Activate
Rows("9:18").EntireRow.Hidden = True
Rows("21:30").EntireRow.Hidden = True
Rows("33:42").EntireRow.Hidden = True
Rows("45:50").EntireRow.Hidden = True

Sheets("GB").Activate
Rows("8:16").EntireRow.Hidden = True
Rows("19:27").EntireRow.Hidden = True
Rows("30:39").EntireRow.Hidden = True
Rows("42:47").EntireRow.Hidden = True

End Sub

Macros and VBA

5 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 25, 2020

    North_Yorks 

    If you want to make the same changes on multiple sheets, a more succinct version might be

    Option Explicit

Private Sub Workbook_Open()
    HideRows
End Sub

Sub HideRows()
Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "GeneralInstructions" Then
            ws.Rows("8:18").Hidden = True
            ws.Rows("21:30").Hidden = True
        End If
    Next
    Sheets("GeneralInstructions").Select
End Sub

    It is the final (or in this case the only) Select that determines what you see when control is returned from the macro. 

    [I didn't bother about EntireRow because the Row object is already the entire row]

    Sub UnhideRows()
Dim ws As Worksheet
    For Each ws In Sheets
        ws.Rows.Hidden = False
    Next
End Sub

     

    Hi Craig Hatmaker  Good to see you around!  Do not hesitate to improve my programming style,  It has a long way to go!

  • Craig Hatmaker's avatar
    Craig Hatmaker
    Iron Contributor
    Nov 25, 2020

    North_Yorks 

     

    Private Sub Workbook_Open()

    Worksheets("General Instructions").Activate

    With Sheets("Americas")
    .Rows("8:18").EntireRow.Hidden = True
    .Rows("21:30").EntireRow.Hidden = True
    .Rows("33:42").EntireRow.Hidden = True
    .Rows("45:49").EntireRow.Hidden = True
    End With

    With Sheets("ANZASAF")
    .Rows("8:18").EntireRow.Hidden = True
    .Rows("21:24").EntireRow.Hidden = True
    .Rows("27:36").EntireRow.Hidden = True
    .Rows("39:45").EntireRow.Hidden = True
    End With

    With Sheets("CEU")
    .Rows("9:18").EntireRow.Hidden = True
    .Rows("21:30").EntireRow.Hidden = True
    .Rows("33:42").EntireRow.Hidden = True
    .Rows("45:50").EntireRow.Hidden = True
    End With

    With Sheets("GB")
    .Rows("8:16").EntireRow.Hidden = True
    .Rows("19:27").EntireRow.Hidden = True
    .Rows("30:39").EntireRow.Hidden = True
    .Rows("42:47").EntireRow.Hidden = True
    End With

    End Sub

    • North_Yorks's avatar
      North_Yorks
      Copper Contributor
      Nov 25, 2020

      Craig Hatmaker 

      Thank you for your response i have added the End With that I was missing and I get this error message when I open the file now

       

      • Craig Hatmaker's avatar
        Craig Hatmaker
        Iron Contributor
        Nov 25, 2020

        North_Yorks 
        I'm not sure I understand what you mean by " added the End With that I was missing". Perhaps you are missing that instead of 

         

        Sheets("Americas").Activate

         

        I'm using 

         

        With Sheets("Americas")

         

        and where you have something like 

         

        Rows("8:18").EntireRow.Hidden = True

         

        I have added a period to the front so it is now:

         

        .Rows("8:18").EntireRow.Hidden = True

         

        I'm using an entirely different approach that happens to look very similar. You are trying to activate the sheet and hoping that the rows will reference the active sheet. I'm explicitly telling Excel to link the rows to the specific worksheet and I'm not activating each sheet to do it. By not activating each worksheet, your first activate remains the active worksheet.

        Without the WITH statement the new code would look like this (which may make things clearer),

         

        Sheets("Americas").Rows("8:18").EntireRow.Hidden = True
Sheets("Americas").Rows("21:30").EntireRow.Hidden = True
Sheets("Americas").Rows("33:42").EntireRow.Hidden = True
Sheets("Americas").Rows("45:49").EntireRow.Hidden = True

Sheets("ANZASAF").Rows("8:18").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("21:24").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("27:36").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("39:45").EntireRow.Hidden = True

         

         

        The WITH statement allows us to not duplicate the Sheets("sheet name") on every row.