How to macro will Unhide all the worksheet except the active sheet

[krunal123]

'This macro will hide all the worksheet except the active sheet
Sub HideAllExceptActiveSheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
Next ws
End Sub

This code its working But We need 'This macro will Unhide all the worksheet except the active sheet

Pleased Help & Share VBA Code........

 

[rollis13]

Well, something like this would do the job:

Sub UnHideAllExceptActiveSheet()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then ws.Visible = True
    Next ws
End Sub

 

[Peter_SSs]

Since the active sheet must be visible to start with (& it doesn't hurt to re-set that visible status), it seems pointless to bother checking the names of the sheets.

Sub UnHideAllSheets()
  Dim ws As Worksheet
 
  For Each ws In Worksheets
    ws.Visible = True
  Next ws
End Sub

 

[OaklandJim]

See if this workbook does what you want.

Worksheet_FollowHyperlink event for the DESKBOARD worksheet:

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim wsLoop As Worksheet
   
    Dim sNavSheetName As String
   
    Dim sSheetName As String
   
    sNavSheetName = "DESKBOARD"
   
    sSheetName = Trim(Target.Name)
   
    If Not WorksheetExists(sSheetName) _
     Then
        MsgBox "Worksheet " & sSheetName & " does not exist.", vbInformation
        Exit Sub
    End If
   
    Worksheets(sSheetName).Visible = True
   
    Worksheets(sNavSheetName).Visible = True

    For Each wsLoop In Worksheets
       
        If wsLoop.Name <> sSheetName And wsLoop.Name <> sNavSheetName _
         Then
             wsLoop.Visible = xlSheetHidden
        End If
   
    Next wsLoop
   
End Sub

Code to check if a worksheet exists. Used so the event code above does not try to open a worksheet that does not exist.

Option Explicit

Function WorksheetExists(psSheetName As String)
    
    Dim wsSheet As Worksheet
    
    WorksheetExists = False
    
    For Each wsSheet In Worksheets
        If psSheetName = wsSheet.Name Then WorksheetExists = True
        Exit Function
    Next wsSheet
    
End Function

 

[OaklandJim]

My apologies. That code does not work. I'll repost when I fix it.

 

[OaklandJim]

Try this version.

Option Explicit

Function WorksheetExists(psSheetName As String) As Boolean
   
    Dim wsTest As Worksheet
   
    WorksheetExists = False
   
    For Each wsTest In ThisWorkbook.Worksheets
        If psSheetName = wsTest.Name _
         Then
            WorksheetExists = True
            Exit For
        End If
   
    Next wsTest
   
End Function

 

[OaklandJim]

Try this version.

Option Explicit

Function WorksheetExists(psSheetName As String) As Boolean
   
    Dim wsTest As Worksheet
   
    WorksheetExists = False
   
    For Each wsTest In ThisWorkbook.Worksheets
        If psSheetName = wsTest.Name _
         Then
            WorksheetExists = True
            Exit For
        End If
   
    Next wsTest
   
End Function

 

[Peter_SSs]

Try this version.

How is it different to the post #6 version?

Also not understanding how your codes are addressing the original question asked.

We need 'This macro will Unhide all the worksheet except the active sheet

 

[OaklandJim]

Peter_SS. I apologize for the redundancy. I'm not sure what I did to post the same thing twice. I did not mean to.

I believe that the corrected workbook that I posted does do what was requested. It is marked as a solution.

 

[Peter_SSs]

Peter_SS. I apologize for the redundancy. I'm not sure what I did to post the same thing twice. I did not mean to.

Fair enough - no real problem. I just couldn't see any difference but thought there must have been something.

I believe that the corrected workbook that I posted does do what was requested. It is marked as a solution.

I can't argue with the last part of that though I am confused by it - for two reasons

• When I first saw the thread post 2, which does something very different to yours, was marked as the solution. Some time later post 3, which also does something very different to yours, was marked as the solution. Now your is.
• Both the thread title and the text & the image in post 1 all include ".. Unhide all the worksheets except .." whereas your code "Hides all the worksheets except .."

In the end of course it is up to the OP, though unfortunately we have not had any feedback from them apart from a number of changes to the marked solution.