VBA Macro = Certain Cell

canthony24

Board Regular
Joined
Mar 24, 2016
Messages
69
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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,845
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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
 

Forum statistics

Threads
1,143,676
Messages
5,720,242
Members
422,272
Latest member
ginkgoVil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top