Macro to Hide/Unhide 3 Sheets

[suriyahi]

Hello,

First timer here. Hoping to get some help and be help to others here.

Would like to create 3 command buttons for 3 sheets that are hidden. Want first command button to unhide Sheet1 and second command button to unhide Sheets2 and third command button to unhide Sheet3. When closing the workbook or when re-open, by default I want these 3 sheets to be hidden again. How can this be possible? Thanks for your help.:banghead:

[GTO]

Where are the three command buttons to be placed? Sheet 4?

[suriyahi]

Yes. Thank you.

[GTO]

I did not have it hide the sheets on close, as then you get to decide whether you want to save or not (and maybe save bad data).

Rather - this simple example hides the sheets during the Open event.

Buttons are as I understood what you were looking to see.

[suriyahi]

Perfect. Thank you. Let me copy this code to my original spreadsheet. I appreciate it very much. Thanks again!!

[suriyahi]

Actually I am stumped now. My actual Sheet names are 'Part Information', 'Vendor Information' and 'Cost and Prices'. How would I accomodate in what you have suggested? Thanks!!

[GTO]

In the 'Master' sheet (or whatever you name it) module, change the arg being passed to the sheet name correlating w/the button; like:

Private Sub CommandButton1_Click()
HideSheet "Part Information"
End Sub

Then in the standard module, remove the line that prefaces the string passed (For the example, I just commented it out so you could see it); like:

Sub HideSheet(strName As String)
'strName = "Sheet" & strName

ThisWorkbook.Worksheets(strName).Visible = xlSheetHidden ' or xlSheetVeryHidden
End Sub



The code under ThisWorkbook stays the same, as it just hides all sheets who's codename starts with "shtH"

Private Sub Workbook_Open()
Dim wksWorksheet As Worksheet

For Each wksWorksheet In ThisWorkbook.Worksheets
If wksWorksheet.CodeName Like "shtH*" Then
wksWorksheet.Visible = xlSheetVeryHidden
End If
Next
ThisWorkbook.Saved = True

End Sub


Workbook attached w/changes. Hopefully this explains it better?

[suriyahi]

Hello...first 3 commands work now but I could not get these 3 hidden sheets to unhide again. But here is the thing....again..I apologize for creating confusion. It seems like I am wasting your time but actually I am learning in new dimention. My requirements are other way around then how the code works.

I will have those 3 sheets pre-hidden and first, second and third command buttons will open Sheets 'Part Information', 'Vendor Information' and 'Cost and Prices'. And, instead of 'Unhide all' command button, it will be 'Hide 3 Sheets' and it will hide them again. And, this is my final requirement. If you have time to help me I will definitely appreciate it. Thanks again.

[GTO]

Suriyahi,

I re-read your first post, and the confusion was my fault, as you clearly stated that you wanted the buttons to unhide the sheets. Sorry about that.

Also, I am a quite new member, and am happy to be of what help I can.

To that end - I have a question before replying. As you are allowing the user to access all sheets, am I correct in presuming that the unhiding/hiding is for the user's convenience?

The reason I ask is that if the user opens the workbook w/macros disabled, either the sheets stay hidden (if they were hidden at the last save) or they stay visible (if they were visible at last save execution).

Mark

[suriyahi]

Hi Mark, thanks for your reply.

Yes, purpose is I have way too many sheets in my workbook. And, those 3 sheets are actually Pivot Tables and I used to have all 3 in same sheet which looked pretty cluttered. So, I wanted those 3 pivot reports into 3 separate sheets. Now, I would like these 3 sheets in questions to be Hidden and unhide them only when user clicks one of the three command buttons. As I think of, we may not need the 4th button if and when user either closes the workbook or saves the file then those 3 sheets gets automatically hidden again. If this is complicated, then we can use the 4th command button that you already have to hide just these three sheets. I hope I explained it with much of rumble. Thanks for your time.

[GTO]

Assuming my brain-dead self has a thought left, I believe this is what you want.

I commented the code up a bit, in case this helps with any mods needed.

Also - I left the fourth command button (with changing its operation of course) so you'll have the choice as to deleting it or not. As the Open event hides all the sheets, you may not want it.

Mark