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]

In your first post

all 75 worksheets are hidden with a password

now you say

I'm not wanting to go the route of using a password.

If this is so you just remove the Password parameter & code line

Sub SheetsVisibility(ByVal Visibility As XlSheetVisibility)
    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)
    Next ws
    Application.ScreenUpdating = True
End Sub

and call it in same manner but just pass the one argument

SheetsVisibility Visibility:=xlSheetVisible

Dave

 

[sduttonusa]

Thanks for all your help. Sorry, using VBA is all new to me. I apologize for not quite understanding everything. I put the top code into a module in VBA, but the macro is not showing up to run. (Not sure what the second box of code is for.)

 

[dmt32]

Thanks for all your help. Sorry, using VBA is all new to me. I apologize for not quite understanding everything. I put the top code into a module in VBA, but the macro is not showing up to run. (Not sure what the second box of code is for.)

The SheetVisibility code will not display in your Macro list as it has a parameter.

You call the Sub from another procedure as I showed in my last post or you can use a button.

For example if you have a button on your sheet to show sheets then it would look like this

Private Sub CommandButton1_Click()
 SheetsVisibility Visibility:=xlSheetVisible
End Sub

The Visibility parameter will display the Intellisense giving you the selection choices for the argument.

Dave

 

[sduttonusa]

Is there a straightforward macro that will make the Array's list of worksheets visible (and all other worksheets VeryHidden)?

 

[dmt32]

Is there a straightforward macro that will make the Array's list of worksheets visible (and all other worksheets VeryHidden)?

Mine is a straightforward marco that meets your original request

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?

Code provided performs both functions (hidden & unhidden) whilst keeping certain sheets visible

I Initially provided an automatic solution which worked using your Username & negated need for buttons but clearly not happy with this idea so showed you how to call the sub from buttons (your original request) and this should do what you want - What is it about solution you are having difficulty with?

Dave

 

[sduttonusa]

I have a series of Macro Buttons on my Customized Ribbon (My Macros) whereby I can with a click of a button, the macro functions. Perhaps I'm not understanding the two sets of codes, but if the first code is not showing up as a macro, I can't attach it to a button on my ribbon. (All my macros are saved in my Personal Macro file.)

 

[dmt32]

I have a series of Macro Buttons on my Customized Ribbon (My Macros) whereby I can with a click of a button, the macro functions. Perhaps I'm not understanding the two sets of codes, but if the first code is not showing up as a macro, I can't attach it to a button on my ribbon. (All my macros are saved in my Personal Macro file.)

As the code has a parameter you have to type it in when assigning to a control.

'SheetsVisibility xlSheetVeryHidden'

or

'SheetsVisibility xlSheetVisible'

Note the single quotes shown in RED.


Dave

 

[sduttonusa]

OK . . . thank you.

 

[Jon von der Heyden]

Cross-posted here: https://www.excelforum.com/excel-pr...o-make-an-array-of-worksheets-veryhidden.html

@aduttonusa - Whilst do not forbid cross-posting here at MrExcel we do require that you provide references to any posts of the same questions at other forums. This is to so that our members can see what else has been suggested or indeed whether or not your problem has been solved elsewhere. Please provide links in future.

Thanks.

 

[sduttonusa]

I apologize. Thanks for letting me know.