Hideing Sheet Tabs

[Avatar]

Greetings,

Can someone tell me why this hides all sheet tabs on all open workbooks instead of just the active one please? I'll be damned if i can see why it's doing it..

Set aWorkbook = ActiveWorkbook

counterstop = 1
Do Until counterstop = Sheets.Count + 1
aWorkbook.Sheets(counterstop).Visible = False
counterstop = counterstop + 1
Loop

 

[goblin]

Be careful when you use ActiveWorkbook. If you are closing workbooks the ActiveWorkbook will change and your code might not be working on the book you assume it is.

At the start of your code, put your ActiveWorkbook into a variable or use ThisWorkbook if the code should only be applied to the workbook it is placed in.

Anyway, here is a bit more modular approach. Try this out:

Sub TestItHide()
   'Place this line in your code to hide.  "Sheet1" is your splash sheet
   VeryHideAllButSplashSheet ThisWorkbook, "Sheet1"
End Sub

Sub TestItUnHide()
   'Place this line in your code to unhide sheets
   UnhideAllSheets ThisWorkbook
End Sub

Public Sub UnhideAllSheets(wb As Workbook)
   Dim sh As Worksheet
   
   For Each sh In wb.Worksheets
      sh.Visible = xlSheetVisible
   Next
End Sub

Public Sub VeryHideAllButSplashSheet(wb As Workbook, splash As String)
   Dim sh As Worksheet
   
   On Error Resume Next
   wb.Worksheets(splash).Activate
   If Err <> 0 Then
      MsgBox "What do you want to do when the splash sheet does not exist?"
      Exit Sub
   End If
   On Error GoTo 0
   ' Splash sheet is active but it might be hidden.  Must be unhidden to hide all others
   wb.Worksheets(splash).Visible = xlSheetVisible
   
   For Each sh In wb.Worksheets
      If sh.Name <> splash Then
         sh.Visible = xlVeryHidden
      End If
   Next
End Sub

 

[Avatar]

Thanks mate, I'll give that a try