• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

Hiding all tabs with VBA when workbook loads

Folks,
I know I can go to Options - Advanced - Display Options for This Workbook and Uncheck "Show sheet Tabs". However, that can be changed by the user. Is there's a VBA way to keep them from doing that once a workbook has been protected? I found some code that I'm looking at but I'm not sure this will meet my objective.

Sub HideTabs()
Dim w As Worksheet
Set w = Worksheets.Add
w.Name = "ListOfSheets"
For x = 1 To Worksheets.Count
    w.Cells(x, 1) = Sheets(x).Name
Next

wSheets = Array("SHEET1", "SHEET2", "SHEET3")
wsstat = Worksheets(wSheets(0)).Visible
If wsstat = xlSheetVisible Then
wsstat = xlSheetHidden
Else
wsstat = xlSheetVisible
End If
For n = 0 To UBound(wSheets)
Worksheets(wSheets(n)).Visible = wsstat
Next n
End Sub

Open in new window

0
Frank Freese
Asked:
Frank Freese
  • 6
  • 5
1 Solution
 
Gregory MillerGeneral ManagerCommented:
The code you have does make the tabs individually hidden and it would require code to unhide them. If you set the Visible attribute to False this applies to the tab. The setting you are referring to actually hides the entire tab bar. This does not affect the individual tabs properties.
0
 
Frank FreeseAuthor Commented:
This is the old ORGINAL code:

w.Name = "ListOfSheets"
For x = 1 To Worksheets.Count
    w.Cells(x, 1) = Sheets(x).Name
Next

wSheets = Array("SHEET1", "SHEET2", "SHEET3")

Open in new window


Here's my new code:
For x = 1 to Worksheets.Count
w.Cell(x)=Sheets(x)
Next

Open in new window


In my ORGINAL code I'd remove line 4 and replace 5 - 7 with my new code. But will it work?
0
 
Frank FreeseAuthor Commented:
Folks,
I tried the code below in the Activate section of the workbook so when the workbook is activated it would hide all tabs. It crashed at line 6 with the following error:

 "Run-time error '1004'
Application-defined or object defined error"

Dim intIndex As Integer
Dim w As Worksheet
Set w = Worksheets.Add

For x = 1 To Worksheets.Count
    w.Cells(x, 1) = Sheets(x)
Next

For x = 1 To Sheets(x)
wSheets = Array(Sheets(x))
wsstat = Worksheets(wSheets(0)).Visible
Next x

If wsstat = xlSheetVisible Then
wsstat = xlSheetHidden
Else
wsstat = xlSheetVisible
End If
For n = 0 To UBound(wSheets)
Worksheets(wSheets(n)).Visible = wsstat
Next n

Open in new window


Again, my objective is that when a workbook is activated all tabs are hidden.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Gregory MillerGeneral ManagerCommented:
put it in the Workbook_Open section instead.
0
 
Frank FreeseAuthor Commented:
My error.
It is in the Workbook_Open section
0
 
Gregory MillerGeneral ManagerCommented:
what are you trying to do in lines 5-7?
I am dense and cannot follow
0
 
Gregory MillerGeneral ManagerCommented:
OK, this works like a champ...

Private Sub Workbook_Open()

For x = 1 To Sheets.Count
    If ThisWorkbook.Sheets(x).Name <> "Sheet1" Then
        ThisWorkbook.Sheets(x).Visible = False
    End If
Next

End Sub

Open in new window


Modify line 4 with the sheet name that you want to remain visible. You have to have one sheet visible.
0
 
Frank FreeseAuthor Commented:
Merry Christmas to you...
At the beginning of this thread I was looking for a way to hide the worksheet tab. It is my fault if I took you in the wrong direction since then and I apologize. It is not the worksheets I'm neding to hide, simple their tabs.
0
 
Frank FreeseAuthor Commented:
You're not going to believe this but all I need to do is in a module;

ActiveWindow.DisplayWorkbookTabs = False

I still have the need to hide sheets later so you've earned that.

Thanks
0
 
Frank FreeseAuthor Commented:
thank you
enjoy the holidays
0
 
Gregory MillerGeneral ManagerCommented:
Thanks, you too!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now