VBA Macro = Certain Cell

[canthony24]

Very new to VBA and wondering if a code can be written that if a certain cell says a particular word, to hide the tabs not associated with that cell.

For example:

Users will select either 'Cases' or 'Pounds' in Cell B2, once they do that, they can click a macro button to hide the "Source Data", "Trend" and "Pounds" worksheets, but keep the "Cases" tab open and the reverse if they select 'Pounds' in B2.

Any help would be appreciated!

 

[Jeffrey Mahoney]

Please post this code in the Sheet Module level of the VBA code. This would be the name of the sheet where cell B2 is selected to change. Alt-F11 to go to VBA, find the name of your workbook in the list on the left. Find the Microsoft Excel Objects and expand the list. Find the name of your sheet with the Cell B2. Double click on it. Paste the code in the window to the right of that. Test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim WS As Worksheet
  Dim Choice As String
  
  If Not Intersect(Target, Range("B2")) Is Nothing Then
    For Each WS In ThisWorkbook.Worksheets
      Choice = UCase(Range("B2").Value)
      Select Case UCase(WS.Name)
        Case Choice
          WS.Visible = xlSheetVisible
        Case "CASES", "POUNDS", "SOURCE DATA", "TREND"
          WS.Visible = xlSheetHidden
      End Select
    Next WS
  End If
  
    
End Sub