• We are receiving reports of members using the private messaging service (Conversations) in ways that break the forum rules:
    • Do not invite another member to take the question off the forum (i.e. do not suggest that they post to a different forum, do not suggest that they email or private message you the problem, do not simply post a link to another forum, unless it is to a specific, relevant, thread). This applies equally to members asking or answering questions.
    • Soliciting business for yourself is not permitted. This is an all volunteer board, so offering solutions in exchange for compensation is not permitted. Likewise, members seeking solutions must not offer compensation for them. If you have an urgent need, check the Consulting Services page.
    Please help us out by clicking the Report link on any messages you receive that violate these rules. Thank you
  • If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.

Using Macros, Hide (with password) and Unhide multiple worksheets

sduttonusa

New Member
Joined
Jan 13, 2016
Messages
43
I have about 90 worksheets in my workbook. About 75 contain sensitive financial information that I don't want available when someone is sent the file. Is there a way to "group" the 75 sensitive worksheets so that with one click of a Macro button, all 75 worksheets are hidden with a password (very hidden I think is the term). And then with another Macro, (after the password is typed), all 75 are made available?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,142
Office Version
2013
Platform
Windows
Hi,
you can do what you want but personally if your data is that sensitive, I would not send the file containing the sheets you do not want to be viewed.
Excel security is very weak & can easily be broken by those who have good understanding or use of third party applications.

I would suggest that you make a copy of of data that you want people to see & just forward that.

Dave
 
Last edited:

sduttonusa

New Member
Joined
Jan 13, 2016
Messages
43
I appreciate your concern for security. The sensitivity is what I would refer to as low level . . . If we can "hide" certain sheets, the recipient is not going to know that. In addition, many calculations go across the worksheets, so removing worksheets is not an option.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,142
Office Version
2013
Platform
Windows
I appreciate your concern for security. The sensitivity is what I would refer to as low level . . . If we can "hide" certain sheets, the recipient is not going to know that. In addition, many calculations go across the worksheets, so removing worksheets is not an option.

You can manage sheet visibility without need for buttons on your sheet

Try following & see if it will do what you want

Place following code in a STANDARD module

Rich (BB code):
Public AdminUser As Boolean
Public Const Password As String = "ABC123"
Sub SheetsVisibility(ByVal Visibility As XlSheetVisibility, Optional ByVal SheetPassword As String)
    Dim ws As Worksheet
    Dim ToBeExcluded As Boolean
    Dim AlwaysVisibleSheets As Variant
    
'enter all sheet names to array that are to remain visible at all times
    AlwaysVisible = Array("Sheet1", "Sheet2", "Sheet3")
    
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        ToBeExcluded = Not IsError(Application.Match(ws.Name, AlwaysVisible, 0))
        ws.Visible = IIf(Not ToBeExcluded, Visibility, xlSheetVisible)
        If ws.Visible Then ws.Unprotect Password:=SheetPassword Else ws.Protect Password:=SheetPassword
    Next ws
    Application.ScreenUpdating = True
End Sub
You will need to change the sheet password shown in RED as required
Also, enter all the sheet names in the array shown in BLUE that will ALWAYS be visible


Place following code in the THISWORKBOOK code page


Rich (BB code):
Private Sub Workbook_Open()
    AdminUser = CBool(Environ("USERNAME") = "dave")
    SheetsVisibility Visibility:=IIf(AdminUser, xlSheetVisible, xlSheetVeryHidden), SheetPassword:=Password
End Sub
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Success Then SheetsVisibility Visibility:=IIf(AdminUser, xlSheetVisible, xlSheetVeryHidden), SheetPassword:=Password
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'hide all sheets not in alwaysvisible array
    SheetsVisibility Visibility:=xlSheetVeryHidden, SheetPassword:=Password
End Sub
Replace my name shown in RED with your network username -

This sets you as an Admin user & should automatically display all sheets unprotected when you open the workbook. For all other users, only the worksheets you specified in the AlwaysVisible array should be displayed.

Hope Helpful

Dave.
 

sduttonusa

New Member
Joined
Jan 13, 2016
Messages
43
Thanks for all your help. Using the Array feature in your code above, how do I modify the following code to "VeryHidden" a list of worksheets:

Code:
[COLOR=#000000][FONT='inherit'][TABLE="width: 723"]
<tbody>[TR]
[TD="class: code"]<code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">Sub</code> <code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">VeryHiddenSelectedSheets()</code>
<code class="vb spaces" style="white-space: pre-wrap; color: rgb(69, 69, 69); box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">Dim</code> <code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">wks </code><code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">As</code> <code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">Worksheet</code>
 
<code class="vb spaces" style="white-space: pre-wrap; color: rgb(69, 69, 69); box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">On</code> <code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">Error</code> <code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">GoTo</code> <code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">ErrorHandler</code>
 
<code class="vb spaces" style="white-space: pre-wrap; color: rgb(69, 69, 69); box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">For</code> <code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">Each</code> <code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">wks </code><code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">In</code> <code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">ActiveWindow.SelectedSheets</code>
<code class="vb spaces" style="white-space: pre-wrap; color: rgb(69, 69, 69); box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">        </code><code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">wks.Visible = xlSheetVeryHidden</code>
<code class="vb spaces" style="white-space: pre-wrap; color: rgb(69, 69, 69); box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">Next</code>
 
<code class="vb spaces" style="white-space: pre-wrap; color: rgb(69, 69, 69); box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">Exit</code> <code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">Sub</code>
 
<code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">ErrorHandler:</code>
<code class="vb spaces" style="white-space: pre-wrap; color: rgb(69, 69, 69); box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">    </code><code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">MsgBox </code><code class="vb string" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: blue !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">"A workbook must contain at least one visible worksheet."</code><code class="vb plain" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">, vbOKOnly, </code><code class="vb string" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: blue !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">"Unable to Hide Worksheets"</code>
<code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: pre-wrap; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; font-weight: bold !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; font-size: 1em !important; min-height: auto !important;">Sub</code>
[/TD]
[/TR]
</tbody>[/TABLE]

[/FONT][/COLOR]
In the code above, I have to select each worksheet one by one to run the VeryHidden Macro. I want a Macro that will VeryHide an Array of worksheets
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,142
Office Version
2013
Platform
Windows
code you have published was produced by another, suggestion will, unless you are the admin user, automatically hide ALL sheets that are not listed in the array - does this not do what you wanted?


Dave
 

sduttonusa

New Member
Joined
Jan 13, 2016
Messages
43
Thanks, Dave, for your quick response. The code I put above allows me to select multiple worksheets, run the above macro, and those selected worksheets will be VeryHidden. Rather than have to click through my workbook of 90 spreadsheets every time I want to hide the same worksheets, I thought the above code could be modified to make an Array of worksheets VeryHidden.

The code you posted above has this line: AlwaysVisible = Array("Sheet1", "Sheet2", "Sheet3") So I would like to modify the following macro:
Code:
Sub VeryHiddenSelectedSheets()
    Dim wks As Worksheet
 
    On Error GoTo ErrorHandler
 
    For Each wks In ActiveWindow.SelectedSheets
        wks.Visible = xlSheetVeryHidden
    Next
 
    Exit Sub
 
ErrorHandler:
    MsgBox "A workbook must contain at least one visible worksheet.", vbOKOnly, "Unable to Hide Worksheets"
End Sub


To have an Array of worksheets that will "VeryHide" with a simple macro.
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,142
Office Version
2013
Platform
Windows
My code already does what you want

You can call it separately if required by just passing the required arguments

Hidden
Code:
SheetsVisibility Visibility:=xlSheetVeryHidden, SheetPassword:="ABC123"

Visible

Code:
SheetsVisibility Visibility:=xlSheetVisible, SheetPassword:="ABC123"
The sheets you include in the array will remain visible

Dave
 

sduttonusa

New Member
Joined
Jan 13, 2016
Messages
43
Thanks. I'm not wanting to go the route of using a password. The macro that works for me making the selected worksheets VeryHidden is the macro I would like to modify to have an array of worksheets VeryHidden
 

sduttonusa

New Member
Joined
Jan 13, 2016
Messages
43
A related question, in the use of an Array, can a sequential group of worksheets be identified? So the Array might list specific worksheets like "Cash Flow", "Financial Position", etc., but also include all worksheets between "Sales of AAA" and "Sales of ZZZ". That way, when I add worksheets in the future between Sales of AAA and Sales of ZZZ, they will automatically be VeryHidden.
 

Forum statistics

Threads
1,077,965
Messages
5,337,441
Members
399,147
Latest member
Raviteja KOTHA

Some videos you may like

This Week's Hot Topics

Top