VBA code to hide multiple tabs at workbook close

S

Spartan300

Board Regular
Joined
Jul 1, 2008
Messages
71
Hello,

I currently have the following code: -

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
ActiveWorkbook.Unprotect Password:="AmenhotepIV"
Sheets("Sheet1").Visible = False
For Each ws In ActiveWorkbook.Worksheets
If UCase(ws.Name) <> "SHEET1" Then ws.Visible = True
ActiveWorkbook.Protect Password:="AmenhotepIV"
Next ws
ThisWorkbook.Save
End Sub

And was wondering how I can have the code hide more than one tab if it is visable when the workbook is closed?

Many thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Use this code snippet in the right place:
Populate the array ShtsToHide with your sheet names - make sure that at least one sheet is visible at any given time.

Code: 
ShtsToHide = Array("Sheet2", "Sheet3")
For Each ShtName In ShtsToHide
    Worksheets(ShtName).Visible = visible
Next ShtName
 
Upvote 0
Here is another option:

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
ActiveWorkbook.Unprotect Password:="AmenhotepIV"
Sheets("Sheet1").Visible = False
For Each ws In ActiveWorkbook.Worksheets
    Select Case UCase(ws.name)
        Case "SHEET1", "SHEET2", "SHEET3", "*insertsheetnameshere*"
            ws.Visible = True
        Case Else
            ws.Visible = False
    End Select
Next ws
ActiveWorkbook.Protect Password:="AmenhotepIV"
ThisWorkbook.Save
End Sub
 
Upvote 0
Sorry....a correction (shown in red):

Rich (BB code): 
For Each ShtName In ShtsToHide
    Worksheets(ShtName).Visible = xlSheetHidden
Next ShtName
 
Upvote 0
Hello~

This was realy very short and useful. can you please tell how to unhide the hidden sheets.
ShtsTounHide = Array("Sheet2", "Sheet3")
For Each ShtName In ShtsTounHide
Worksheets(ShtName).Visible = visible
Next ShtName

is the above one correct or do we have any extra step for this.. please help..

Thanks,
Pavan..

Mala said:
Use this code snippet in the right place:
Populate the array ShtsToHide with your sheet names - make sure that at least one sheet is visible at any given time.

Code: 
ShtsToHide = Array("Sheet2", "Sheet3")
For Each ShtName In ShtsToHide
    Worksheets(ShtName).Visible = visible
Next ShtName
Click to expand...
 
Upvote 0
I'm looking for a code which closes certain worksheets at the click of a command button. I know how to make all sheets visible, but making select ones hidden seems to be tricky.

Any help will be appreciated.
 
Upvote 0
change this
Worksheets(ShtName).Visible = visible

to this
Worksheets(ShtName).Visible = true
makes it visible
Worksheets(ShtName).Visible = false
makes it hidden
 
Upvote 0
Hey guys,

I used you code which I found here:

Code: 
 Sub Hide_button()
ShtsToHide = Array("Top 22 2015", "Top 22 2015 LW")
For Each ShtName In ShtsToHide
Worksheets(ShtName).Visible = Visible
Next ShtName
End Sub

And I have "Run-time error '9': Subscript out of range"

debug:
Code: 
Worksheets(ShtName).Visible = False


The funny thing is, that when I checked this code on small file just with few tabs it worked perfectly, when I rewrote it for the purpose of my big file with 20 tabs to hide and it showed me this error (but executed the code properly and hid all of the tabs)

Could you help me with this?

Matt
 
Upvote 0
Hey guys,

sorry for double comment, but I couldn't (I don't why) my previous one.

I solved my problem there (I had a mistake in the name of the tab), what made me wonder; is it possible to change this code:

Code: 
 Sub Hide_button()
ShtsToHide = Array("Sheet1","Sheet2")
For Each ShtName In ShtsToHide
Worksheets(ShtName).Visible = False
Next ShtName
End Sub

So it will only hide tabs that are marked in certain color e.g. red?
So I don't have to update the names of the multiple tabs in the code, just change their color.


Sorry for problem,
and I am looking forward for your suggestions.

Matt
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
username access to different worksheet VBA
Replies
4
Views
305
cilantro00
C
I
  • Question Question
VBA to detect hidden rows. then Re-hide
Replies
7
Views
165
Skyybot
Skyybot
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
539
MagicCarpet
M
M
  • Question Question
Not running code
Replies
0
Views
212
MagicCarpet
M
C
  • Question Question
If User Selects Cancel All Sheets are Unprotected
Replies
4
Views
151
christianbiker
C

Forum statistics

Threads
1,224,542
Messages
6,179,424
Members
452,914
Latest member
echoix

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top