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.

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

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