Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and/or Unhide Worksheets
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and/or Unhide Worksheets
Hi Rob
You could try this. Sub Merchant_visible() Dim ws As Worksheet Sheets("merchant").Visible = -1 For Each ws In Worksheets If ws.Name < "mercnant" Then Sheets(ws.Name).Visible = 2 End If Next End Sub "Rob" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and/or Unhide Worksheets
One way to hide all except "Vendor".
Sub HideAllExceptVendor() Sheets("Vendor").Visible=True For i = 1 To Worksheets.Count If Sheets(i).Name < "Vendor" Then Sheets(i).Visible = False End If Next End Sub To Hide all except "Vendor" and "Merchant" Sub HideAllExceptVendorAndMerchant() Sheets("Vendor").Visible = True Sheets("Merchant").Visible = True For i = 1 To Worksheets.Count If Sheets(i).Name < "Vendor" Then If Sheets(i).Name < "Merchant" Then Sheets(i).Visible = False End If End If Next End Sub HTH, Paul "Rob" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and/or Unhide Worksheets
Give this a try...
Sub Test() Call HideAllSheets("That") End Sub Private Sub HideAllSheets(ByVal SheetName As String) Dim wks As Worksheet If SheetExists(SheetName) Then Sheets(SheetName).Visible = xlSheetVisible For Each wks In Worksheets If wks.Name < SheetName Then wks.Visible = xlSheetHidden Next wks Else MsgBox "Error on sheet name." End If End Sub Public Function SheetExists(SName As String, _ Optional ByVal Wb As Workbook) As Boolean 'Chip Pearson On Error Resume Next If Wb Is Nothing Then Set Wb = ThisWorkbook SheetExists = CBool(Len(Wb.Sheets(SName).Name)) End Function -- HTH... Jim Thomlinson "Rob" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and/or Unhide Worksheets
I would do it simply that hide all sheets, then unhide only the one(s) you
want to, so no if statement needed in the for loop. Just hide 'em all then unhide the desired. Before that hiding or unhiding the first time I would add: application.screenupdating = false And then after the changes have been made application.screenupdating = true. That way the user doesn't see worksheets disappearing and reappearing, just a flash from where they are to where they are going. "PCLIVE" wrote: One way to hide all except "Vendor". Sub HideAllExceptVendor() Sheets("Vendor").Visible=True For i = 1 To Worksheets.Count If Sheets(i).Name < "Vendor" Then Sheets(i).Visible = False End If Next End Sub To Hide all except "Vendor" and "Merchant" Sub HideAllExceptVendorAndMerchant() Sheets("Vendor").Visible = True Sheets("Merchant").Visible = True For i = 1 To Worksheets.Count If Sheets(i).Name < "Vendor" Then If Sheets(i).Name < "Merchant" Then Sheets(i).Visible = False End If End If Next End Sub HTH, Paul "Rob" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and/or Unhide Worksheets
This works to show only one sheet. Though you could possible setup something
like a list of sheets, and pass in a class module that contains an array. And the if statement would check to see if any sheet that is in the array is the current sheet, then don't hide it. I like the solution below though, it's portable, and is designed for expansability. "Jim Thomlinson" wrote: Give this a try... Sub Test() Call HideAllSheets("That") End Sub Private Sub HideAllSheets(ByVal SheetName As String) Dim wks As Worksheet If SheetExists(SheetName) Then Sheets(SheetName).Visible = xlSheetVisible For Each wks In Worksheets If wks.Name < SheetName Then wks.Visible = xlSheetHidden Next wks Else MsgBox "Error on sheet name." End If End Sub Public Function SheetExists(SName As String, _ Optional ByVal Wb As Workbook) As Boolean 'Chip Pearson On Error Resume Next If Wb Is Nothing Then Set Wb = ThisWorkbook SheetExists = CBool(Len(Wb.Sheets(SName).Name)) End Function -- HTH... Jim Thomlinson "Rob" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide/Unhide worksheets upon entries in a range of the main worksheet | Excel Discussion (Misc queries) | |||
hide/unhide | Excel Discussion (Misc queries) | |||
How do I hide and unhide worksheets in Excel 2007? | Excel Discussion (Misc queries) | |||
Hide and unhide worksheets | Excel Programming | |||
Hide/Unhide worksheets help | Excel Programming |