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

[sduttonusa]

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]

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

 

[sduttonusa]

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]

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

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

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]

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:

[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

 

[dmt32]

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]

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:

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.

 

[dmt32]

My code already does what you want

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

Hidden

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

Visible

SheetsVisibility Visibility:=xlSheetVisible, SheetPassword:="ABC123"

The sheets you include in the array will remain visible

Dave

 

[sduttonusa]

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]

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.