Hiding Worksheets with VBE
Posted by Stan on September 26, 2001 10:42 AM
I am developing an application with many worksheets but I only want one worksheet open at a time. Typically I would use the following:
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Visible = True
Is there another way of doing this without always having to list all sheets as above? For insance a command line that would close any worksheet that is now open followed by a specification of wha sheet to remain open.
Cheers
Posted by Barrie Davidson on September 26, 2001 11:17 AM
Stan, what is your criteria to determine which sheet will be visible?
BarrieBarrie Davidson
Posted by Stan on September 26, 2001 11:42 AM
Barrie
I have a UserForm as a Main Menu. When it is showing, the WorkBook is hidden. When a selection is made from the menu, the WorkBook is shown and a particular worksheet is "activated" and made visible (all other sheets are hidden). When I return to the Main Menu the Workbook is hidden again. Say I choose another menu item. The WorkBook is activated again and I want another Worksheet to be visible - but I also want the other worksheets hidden (e.g. the one I had previously viewed). Is this clear?
Stan
Posted by Barrie Davidson on September 26, 2001 12:06 PM
Stan, I think the following code will do what you require. It assumes that you will declare the sheet name to unhide via a variable named "unhideName" and then it will cycle through all the sheets making sure that there is only one sheet visible.
Dim count As Integer, counter As Integer
Dim unhideName As String
'Your code to get sheet name
Sheets(unhideName).Visible = True
count = ActiveWorkbook.Sheets.count
For counter = 1 To count
If Sheets(counter).Name <> unhideName Then Sheets(counter).Visible = False
Next counter
' the rest of your code
Hope this is what you need.
Regards,
Barrie
Barrie Davidson
Posted by Stan on September 26, 2001 12:21 PM
Thanks Barrie.
Stan
