Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and Unhiding Worksheets
How do I hide any worksheets that are hidden. In some of my macros, I am
creating worksheets in a workbook but at certain instances all of these worksheets may not be created and others may. I want to just specify unhide all or hide all. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and Unhiding Worksheets
You can't hide all, since at least one worksheet needs to be visible all the
time. Dim sh as worksheet list = Array(1,5,8,9,10) for i =lbound(list) to ubound(list) set sh = Nothing On Error Resume Next set sh = worksheets(list(i)) On Error goto 0 if not sh is nothing then sh.Visible = xlSheetVisible Next -- REgards, Tom Ogilvy "Rob" wrote in message ... How do I hide any worksheets that are hidden. In some of my macros, I am creating worksheets in a workbook but at certain instances all of these worksheets may not be created and others may. I want to just specify unhide all or hide all. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and Unhiding Worksheets
Also, I can't name the Worksheets because at times the names will be
different as I continue creating this tool. So basically what I want to do is to say hide all worksheets except Vendor Worksheet or Merchant Worksheet or Vendor Worksheet and Procurement Worksheet! Thanks Again "Tom Ogilvy" wrote: You can't hide all, since at least one worksheet needs to be visible all the time. Dim sh as worksheet list = Array(1,5,8,9,10) for i =lbound(list) to ubound(list) set sh = Nothing On Error Resume Next set sh = worksheets(list(i)) On Error goto 0 if not sh is nothing then sh.Visible = xlSheetVisible Next -- REgards, Tom Ogilvy "Rob" wrote in message ... How do I hide any worksheets that are hidden. In some of my macros, I am creating worksheets in a workbook but at certain instances all of these worksheets may not be created and others may. I want to just specify unhide all or hide all. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and Unhiding Worksheets
Maybe...
Option Explicit sub testme() Dim sh as worksheet worksheets("Merchant").visible = xlsheetvisible worksheets("vendor").visible = xlsheetvisible Worksheets("procurement").visible = xlsheetvisible for each sh in activeworkbook.worksheets select case lcase(sh.name) case is = "merchant","vendor","procurement" 'do nothing case else sh.visible = xlsheethidden end select next sh end sub Rob wrote: Also, I can't name the Worksheets because at times the names will be different as I continue creating this tool. So basically what I want to do is to say hide all worksheets except Vendor Worksheet or Merchant Worksheet or Vendor Worksheet and Procurement Worksheet! Thanks Again "Tom Ogilvy" wrote: You can't hide all, since at least one worksheet needs to be visible all the time. Dim sh as worksheet list = Array(1,5,8,9,10) for i =lbound(list) to ubound(list) set sh = Nothing On Error Resume Next set sh = worksheets(list(i)) On Error goto 0 if not sh is nothing then sh.Visible = xlSheetVisible Next -- REgards, Tom Ogilvy "Rob" wrote in message ... How do I hide any worksheets that are hidden. In some of my macros, I am creating worksheets in a workbook but at certain instances all of these worksheets may not be created and others may. I want to just specify unhide all or hide all. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and Unhiding Worksheets
Maybe I can be more explicit. I want to unhide the Vendor Worksheet only
(In this case it could be a Merchant Worksheet, Or a Procurement Worksheet) I don't want to specify names of other worksheets that need to be hidden for some may not be created as yet. I want to specify one sheet to be visible in separate macros. So one macro would say unhide the Vendor Worksheet and hide the rest. Another might say unhide the Merchant Worksheet and the Vendor Worksheet and hide the rest. "Tom Ogilvy" wrote: You can't hide all, since at least one worksheet needs to be visible all the time. Dim sh as worksheet list = Array(1,5,8,9,10) for i =lbound(list) to ubound(list) set sh = Nothing On Error Resume Next set sh = worksheets(list(i)) On Error goto 0 if not sh is nothing then sh.Visible = xlSheetVisible Next -- REgards, Tom Ogilvy "Rob" wrote in message ... How do I hide any worksheets that are hidden. In some of my macros, I am creating worksheets in a workbook but at certain instances all of these worksheets may not be created and others may. I want to just specify unhide all or hide all. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and Unhiding Worksheets
Hi Rob,
Maybe this works: Sub UnhideSheet(strNamesToUnhide As String) ' parameter strNamesToUnhide is spaceseparated list of ' sheetnames to unhide Dim shLoop As Worksheet Dim iPos As Integer ' make sure the first named sheet is visible iPos = InStr(1, strNamesToUnhide, " ", vbTextCompare) If iPos = 0 Then ' only one sheet in the list Worksheets(strNamesToUnhide).Visible = xlSheetVisible Else ' use only the first name Worksheets(Left(strNamesToUnhide, iPos - 1)).Visible = xlSheetVisible End If ' Loop thue all sheets to hide / unhide For Each shLoop In ActiveWorkbook.Worksheets If InStr(1, strNamesToUnhide, shLoop.Name, vbTextCompare) = 0 Then shLoop.Visible = xlSheetHidden Else shLoop.Visible = xlSheetVisible End If Next shLoop End Sub Sub TestMe1() Call UnhideSheet("Sheet1") End Sub Sub TextMe2() Call UnhideSheet("Sheet2 Sheet3") End Sub Executor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding/unhiding rows | Excel Worksheet Functions | |||
Hiding/unhiding rows | Excel Discussion (Misc queries) | |||
Hiding Worksheets and Unhiding them easily for Novice User | Excel Discussion (Misc queries) | |||
Hiding/UnHiding Selected Worksheets | Excel Programming | |||
Hiding/Unhiding | Excel Worksheet Functions |