Hide unselected sheets

[RunRabbit13]

I am trying to create a button on the ribbon that will allow the user to hide only sheets not selected. For example, if I have workbook with [Sheet1, Sheet2, Sheet3, Sheet4] and the user has selected [Sheet1 & Sheet3] then [Sheet2 & Sheet4] would hide. I have the VBA to hide all (except the active sheet) and VBA to unhide all. I want this to be dynamic so the user can use it across multiple workbooks with different nomenclature for the sheets. Any help is greatly appreciated.

 

[Rick Rothstein]

Give this macro code a try...

Sub HideUnselectedSheets()
  Dim X As Long, InSelection As String, WS As Worksheet
  With ActiveWindow.SelectedSheets
    For X = 1 To .Count
      InSelection = InSelection & "/" & .Item(X).Name
    Next
  End With
  For Each WS In Worksheets
    If InStr(InSelection & "/", "/" & WS.Name & "/") = 0 Then WS.Visible = xlSheetHidden
  Next
End Sub

 

[RunRabbit13]

Hi Rick,

This worked beautifully. If you have the time or care to share can you step through the code? The majority of the time I can muddle my way through code but I have never seen anyone use items such as "/". If not, I really appreciate what you have done. I kept trying to define my sheets as an array which was leading me towards a bottle of Tylenol.

 

[Rick Rothstein]

Hi Rick,

This worked beautifully. If you have the time or care to share can you step through the code? The majority of the time I can muddle my way through code but I have never seen anyone use items such as "/". If not, I really appreciate what you have done. I kept trying to define my sheets as an array which was leading me towards a bottle of Tylenol.

The slashes are simply a character that cannot appear in a worksheet name (any such character can be used). What the code does is form a slash delimited text string from all the selected sheets and inside the loop, a trailing slash is concatenated so the whole text string looks like this...

/Sheet3/Sheet5/Sheet6/Sheet8/

just to use some made up selected sheet names. Then, in the loop, we iterate each sheet name, concatenating slashes on each end and seeing if we can find that inside the text string above (formed from the selected sheets)... if we cannot find it (InStr returns 0), then we hide the sheet. The reason for all the slashes is to stop InStr from finding a partial match. For example, lets say a sheet named "Project Managers" was selected but a sheet named "Managers" was not. If you just looked up "Managers" by itself, it would be found inside of "Project Managers" and not hidden; but with slashes around all names, InStr would only find the sheet names as uniques, non-embedded text.