Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and Unhiding Sheets
Thanks for that quick response, however, it says i have a compile error
without for. What I am trying to do is to hide all the worksheets except one called "Vendor Worksheet" after it runs another macro first. I want to include this "fix" at the end of the macro. I understand now that I can't hide all and I had the macro doing that and trying to make the Vendor worksheet visible afterwards. That of course will not work. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and Unhiding Sheets
Hi Rob,
I have tried something out: Sub HideShowAll(strSkip As String, blnShow As Boolean) Dim sht As Worksheet ' Start error handling On Local Error GoTo hideAll_err ' Activate the sheet that not must be hidden, ' if it does not exists an error occurs If Not blnShow Then Sheets(strSkip).Activate End If For Each sht In ThisWorkbook.Worksheets If StrComp(sht.Name, strSkip, vbTextCompare) < 0 Then sht.Visible = blnShow End If Next ' cancel error handling On Local Error Resume Next ' Activate the wanted sheet, ' If if exists it is now visible If blnShow Then Sheets(strSkip).Activate End If Exit Sub hideAll_err: MsgBox "Sheet not found", vbCritical, strSkip End Sub You can use this separate Sub for Showing All and Hiding all but one.\ In the case ad hand you coud use Call HideShowAll("Vendor Worksheet", False) Hoop This Helps Executor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and Unhiding Sheets
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. "Executor" wrote: Hi Rob, I have tried something out: Sub HideShowAll(strSkip As String, blnShow As Boolean) Dim sht As Worksheet ' Start error handling On Local Error GoTo hideAll_err ' Activate the sheet that not must be hidden, ' if it does not exists an error occurs If Not blnShow Then Sheets(strSkip).Activate End If For Each sht In ThisWorkbook.Worksheets If StrComp(sht.Name, strSkip, vbTextCompare) < 0 Then sht.Visible = blnShow End If Next ' cancel error handling On Local Error Resume Next ' Activate the wanted sheet, ' If if exists it is now visible If blnShow Then Sheets(strSkip).Activate End If Exit Sub hideAll_err: MsgBox "Sheet not found", vbCritical, strSkip End Sub You can use this separate Sub for Showing All and Hiding all but one.\ In the case ad hand you coud use Call HideShowAll("Vendor Worksheet", False) Hoop This Helps Executor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding / Unhiding Sheets | Excel Discussion (Misc queries) | |||
Hiding and unhiding sheets | Excel Discussion (Misc queries) | |||
Hiding/Unhiding multiple sheets in VBA? | Excel Programming | |||
Hiding/Unhiding sheets and rows (An easy one for most of you....) | Excel Programming | |||
Hiding and Unhiding work sheets | Excel Programming |