VBA- Sheet hide and unhide

[prasanna]

Hi Experts,

I need help from this forum,

I have 21 sheet it may be increased to 35.
when i click on any sheet for example sheet7 rest all the sheets should be hide except sheet name "start" sheet. is it possible..? if yes kinly help me.

PFA sample file and do the needful.

Thanks
Prasanna

 

[Luke M]

Hi Prasanna,

In the ThisWorkbook module of VBA, put this code

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws As Worksheet

Application.ScreenUpdating = False

If Sh.Name = "Start" Then
    'unhide every sheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = True
    Next ws
Else
    For Each ws In ThisWorkbook.Worksheets
        'Hide every sheet except the one selected, and Start sheet
        If ws.Name <> "Start" And ws.Name <> Sh.Name Then
            ws.Visible = False
        End If
    Next ws
End If
Application.ScreenUpdating = True
End Sub

Example showing which one is the ThisWorkbook module

 

[Hui]

Similar to Lukes:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

With Application
  .ScreenUpdating = False
  .DisplayAlerts = False
End With

For mySh = 1 To Sheets.Count
  If Sheets(mySh).Name = "Start" Or Sheets(mySh).Name = ActiveSheet.Name Then
  Sheets(mySh).Visible = True
  Else
  Sheets(mySh).Visible = False
  End If
Next mySh
 
With Application
  .ScreenUpdating = True
  .DisplayAlerts = True
End With
 
End Sub

I have also included some code to unhide all the worksheets if required

Sub UnHideAll()

Dim mySh As Integer

For mySh = 2 To Sheets.Count
  ThisWorkbook.Sheets(mySh).Visible = True
Next mySh
Application.EnableEvents = False
Sheets("Start").Select
Application.EnableEvents = True

End Sub

Put both in the same location as recommended by Luke

 

[prasanna]

My dear Luke & Hui,

Wow its amazing... Thank you so much. Appreciate..
I m learning lot... from this forum, I love this forum & I have a confident , one day I m also become one of the Ninja's in this forum.

Regards
Prasanna