Trying to combine code on ThisWorkbook to change all worksheets contained

[TheJay]

Compile error: Variable not defined

             x = ActiveSheet.Name

I can't see where I have gone wrong.

Private Sub Workbook_SheetActivate(ByVal wks As Object)
            With Application
             x = ActiveSheet.Name
            For Each wks In Worksheets
            Sheets(wks.Name).Select
            ActiveWindow.DisplayHeadings = False
            ActiveWindow.DisplayHorizontalScrollBar = False
            ActiveWindow.DisplayVerticalScrollBar = True
            .DisplayFullScreen = True
            .DisplayFormulaBar = False
            .DisplayStatusBar = False
            .CommandBars("Full Screen").Visible = False
            .CommandBars("Worksheet Menu Bar").Enabled = False
            .CommandBars("Standard").Visible = False
            .CommandBars("Formatting").Visible = False
            Next wks
            Sheets(x).Select
            End With
End Sub

 

[breynolds0431]

Compile error: Variable not defined

             x = ActiveSheet.Name

I can't see where I have gone wrong.

Private Sub Workbook_SheetActivate(ByVal wks As Object)
            With Application
             x = ActiveSheet.Name
            For Each wks In Worksheets
            Sheets(wks.Name).Select
            ActiveWindow.DisplayHeadings = False
            ActiveWindow.DisplayHorizontalScrollBar = False
            ActiveWindow.DisplayVerticalScrollBar = True
            .DisplayFullScreen = True
            .DisplayFormulaBar = False
            .DisplayStatusBar = False
            .CommandBars("Full Screen").Visible = False
            .CommandBars("Worksheet Menu Bar").Enabled = False
            .CommandBars("Standard").Visible = False
            .CommandBars("Formatting").Visible = False
            Next wks
            Sheets(x).Select
            End With
End Sub

Need to declare the x variable

Try this:

Private Sub Workbook_SheetActivate(ByVal wks As Object)

Dim x As String

            With Application

             x = ActiveSheet.Name

            For Each wks In Worksheets
            
                      Sheets(wks.Name).Select
          
                      ActiveWindow.DisplayHeadings = False
          
                      ActiveWindow.DisplayHorizontalScrollBar = False
          
                      ActiveWindow.DisplayVerticalScrollBar = True
          
                      .DisplayFullScreen = True
          
                      .DisplayFormulaBar = False
          
                      .DisplayStatusBar = False
          
                      .CommandBars("Full Screen").Visible = False
          
                      .CommandBars("Worksheet Menu Bar").Enabled = False
          
                      .CommandBars("Standard").Visible = False
          
                      .CommandBars("Formatting").Visible = False
            Next wks

            Sheets(x).Select

            End With

End Sub

 

[TheJay]

Need to declare the x variable

Try this:

Private Sub Workbook_SheetActivate(ByVal wks As Object)

Dim x As String

            With Application

             x = ActiveSheet.Name

            For Each wks In Worksheets
           
                      Sheets(wks.Name).Select
         
                      ActiveWindow.DisplayHeadings = False
         
                      ActiveWindow.DisplayHorizontalScrollBar = False
         
                      ActiveWindow.DisplayVerticalScrollBar = True
         
                      .DisplayFullScreen = True
         
                      .DisplayFormulaBar = False
         
                      .DisplayStatusBar = False
         
                      .CommandBars("Full Screen").Visible = False
         
                      .CommandBars("Worksheet Menu Bar").Enabled = False
         
                      .CommandBars("Standard").Visible = False
         
                      .CommandBars("Formatting").Visible = False
            Next wks

            Sheets(x).Select

            End With

End Sub

Thank you for your reply. The code appears to be fine until executed, then it created an infinite loop and I can't understand why? There are three worksheets, so why doesn't it update the three and then stop?

 

[TheJay]

Endless loop

Private Sub Workbook_SheetActivate(ByVal wks As Object)
    Application.ScreenUpdating = False
        Dim x As String
            With Application
                x = ActiveSheet.Name
                    For Each wks In Worksheets
                        Sheets(wks.Name).Select
                            ActiveWindow.DisplayHeadings = False
                            ActiveWindow.DisplayHorizontalScrollBar = False
                            ActiveWindow.DisplayVerticalScrollBar = True
                            .DisplayFullScreen = True
                            .DisplayFormulaBar = False
                            .DisplayStatusBar = False
                            .CommandBars("Full Screen").Visible = False
                            .CommandBars("Worksheet Menu Bar").Enabled = False
                            .CommandBars("Standard").Visible = False
                            .CommandBars("Formatting").Visible = False
                    Next wks
                            Sheets(x).Select
            End With
    Application.ScreenUpdating = True
End Sub

 

[breynolds0431]

Endless loop

Private Sub Workbook_SheetActivate(ByVal wks As Object)
    Application.ScreenUpdating = False
        Dim x As String
            With Application
                x = ActiveSheet.Name
                    For Each wks In Worksheets
                        Sheets(wks.Name).Select
                            ActiveWindow.DisplayHeadings = False
                            ActiveWindow.DisplayHorizontalScrollBar = False
                            ActiveWindow.DisplayVerticalScrollBar = True
                            .DisplayFullScreen = True
                            .DisplayFormulaBar = False
                            .DisplayStatusBar = False
                            .CommandBars("Full Screen").Visible = False
                            .CommandBars("Worksheet Menu Bar").Enabled = False
                            .CommandBars("Standard").Visible = False
                            .CommandBars("Formatting").Visible = False
                    Next wks
                            Sheets(x).Select
            End With
    Application.ScreenUpdating = True
End Sub

It may be the Sheets(wks.Name).Select, which I'm not sure what that's attempting to do. It looks like that's attempting to select the active sheet in the loop, which wouldn't be necessary as it's already the activesheet. From my testing, when I exclude this part, there's no longer any endless loop.

Also, other changes I made was to the ByVal Sh As object, which is the default when I select that from the workbook declarations drop down. I then declared wks as a worksheet.

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.ScreenUpdating = False
Dim x As String, wks As Worksheet

With Application
    x = ActiveSheet.Name
        For Each wks In Worksheets
            'Sheets(wks.Name).Select
            ActiveWindow.DisplayHeadings = False
            ActiveWindow.DisplayHorizontalScrollBar = False
            ActiveWindow.DisplayVerticalScrollBar = True
            .DisplayFullScreen = True
            .DisplayFormulaBar = False
            .DisplayStatusBar = False
            .CommandBars("Full Screen").Visible = False
            .CommandBars("Worksheet Menu Bar").Enabled = False
            .CommandBars("Standard").Visible = False
            .CommandBars("Formatting").Visible = False
        Next wks
    Sheets(x).Select
End With
Application.ScreenUpdating = True

End Sub

 

[TheJay]

Thank you very much. It didn't need much changing, but enough to make all the difference. It was good that you also declared wks in the beginning to make it even shorter.