In ms excel i want a macro to hide all visible sheets.?

Question

I want to display sheet that depend on results from else weare in the workbook. that is fine. but i need a reset button that will hide all the sheets.

I dont know wht sheets will be visible becouse that will be visible at the time.

Any ideas?

Mark is right i cant hide all sheets. i want sheet 1 visible and the remaining to be hidden.

Answer 1

This would work, except for the fact that you can't hide ALL of the sheets.

for x = 1 to ActiveWorkBook.WorkSheets.count
ActiveWorkBook.Sheets(x).visible = false
next x


[Edit] - to skip a specific sheet

for x = 1 to ActiveWorkBook.WorkSheets.count
if ActiveWorkBook.Sheets(x).Name <> "Sheet1" then
ActiveWorkBook.Sheets(x).visible = false
end if
next x

Answer 2

Mark is right. His code below is almost correct.

for x = 1 to ActiveWorkBook.WorkSheets.count
ActiveWorkBook.Sheets(x).visible = false
next x

There are two different good ways that you could change the code and make it work.

For x = 2 To ActiveWorkbook.WorkSheets.Count
ActiveWorkbook.Worksheets(x).Visible = xlSheetVeryHidden
Next x

I changed three things for this variation.

1.) I started out with x = 2 instead of x = 1 to leave the worksheet tab to the far left visible.
2.) Notice that I used Worksheets(x).Visible instead of Sheets(x).Visible. The Sheets collection refers to all of the sheets, including the chart sheets. The Worksheets collection refers to JUST the Worksheets. Mark shouldn't have used the Worksheets collection to count then the Sheets collection to determine visibility because the if you have chart sheets the count won't end up the same. You want to use the same collectio for both and which one you use will depend on whether you want to hide any chart sheets also.
3.) Notice that I used xlSheetVeryHidden instead of the value of False. If the worksheets are just hidden then someone can pull them up in Excel if they are smart enough and know how to find the hidden worksheets. With xlVeryHidden you can only unhide the worksheets with a macro and by setting their visibility back to true.

Dim x As Worksheet
Dim MyCheck As Boolean

MyCheck = False

For Each x In Worksheets
If x.CodeName = "MySheet" Then
MyCheck = True
Exit For
End If
Next x

If MyCheck = False Then
MsgBox ("The referenced worksheet has been deleted or removed.")
Exit Sub
End If

For Each x In Worksheets
If Not x.CodeName = "MySheet" Then
x.Visible = xlSheetVeryHidden
End If
Next x



There's one nice thing about this 2nd macro that the first one that I gave you doesn't use. For the 2nd macro it doesn't matter at what position the worksheet is at or what the user has decided to call it. By referencing the code name of the worksheet it will make sure the macro leaves the right worksheet visible. The code name of the worksheet is the name outside of the parenthesis in the Project Explorer window in Visual Basic Explorer. The name inside the parenthesis is the tab name or just Name of the worksheet. Changing the Name (named displayed on the worksheet tab) won't change the code name for the worksheet and vice versa. The second macro I wrote also does a second thing to check to make sure that the user didn't for some reason delete the worksheet.

Answer 3

Instead of a macro to hide sheets, another way might be to use conditional formatting so that when your specific results appear elsewhere in the workbook, all other cell contents are changed to white font making them invisible rather than the full sheet? That might be a solution.

Answer 4

particularly of a macro to conceal sheets, yet otherwise must be to coach conditional formatting so as that when your specific outcomes look someplace else interior the workbook, all diverse cellular contents are replaced to white font making them invisible particularly than the great sheet? that ought to prefer to be a answer.