Solved

Hiding all tabs with VBA when workbook loads

Posted on 2013-12-24
11
319 Views
Last Modified: 2013-12-25
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
Comment
Question by:Frank Freese
  • 6
  • 5
11 Comments
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39738655
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
 

Author Comment

by:Frank Freese
ID: 39738666
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
 

Author Comment

by:Frank Freese
ID: 39738777
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
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39738780
put it in the Workbook_Open section instead.
0
 

Author Comment

by:Frank Freese
ID: 39738783
My error.
It is in the Workbook_Open section
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 11

Expert Comment

by:Technodweeb
ID: 39738788
what are you trying to do in lines 5-7?
I am dense and cannot follow
0
 
LVL 11

Accepted Solution

by:
Technodweeb earned 500 total points
ID: 39738809
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
 

Author Comment

by:Frank Freese
ID: 39739133
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
 

Author Comment

by:Frank Freese
ID: 39739146
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
 

Author Closing Comment

by:Frank Freese
ID: 39739149
thank you
enjoy the holidays
0
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39739158
Thanks, you too!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now