How do I unhide multiple sheets based off a single cell value

P

Pizza_Man

New Member
Joined
Jan 5, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm hoping someone can help me with this. I've never really done VBA code before so I'm struggling to write something that works the way I intend it to. I have a drop down list in a cell on my "Client information" sheet (in cell C16). I have 7 total options - Please Select, 1, 2, 3, 4, 5 & 6. I then have 6 sheets named "Gift 1", "Gift 2".... etc. I would like all the gift sheets to be hidden when the Please Select option is chosen. If the sales rep chooses 1 from the list, I'd like only the Gift 1 sheet to appear. If they choose 2 from the list, I'd like Gift 1 & Gift 2 to appear and so on. I wrote the code below by lots of google searches, lots of copying & pasting and lots of blind tinkering. I managed to get this to work but only if each time a new selection is made from the list you manually run the macro. I'd like the macro to automatically run anytime a new selection is made from the drop down list. Can someone please advise me on what changes need to be made in order for this to happen automatically? It would be a bonus if someone could show me how to shorten this long code

Sub GiftTabs()
Select Case Worksheets("Client Information").Range("C16").Value
Case "Please Select"
Sheets("Gift 1").Visible = False
Sheets("Gift 2").Visible = False
Sheets("Gift 3").Visible = False
Sheets("Gift 4").Visible = False
Sheets("Gift 5").Visible = False
Sheets("Gift 6").Visible = False
Case "1"
Sheets("Gift 1").Visible = True
Sheets("Gift 2").Visible = False
Sheets("Gift 3").Visible = False
Sheets("Gift 4").Visible = False
Sheets("Gift 5").Visible = False
Sheets("Gift 6").Visible = False
Case "2"
Sheets("Gift 1").Visible = True
Sheets("Gift 2").Visible = True
Sheets("Gift 3").Visible = False
Sheets("Gift 4").Visible = False
Sheets("Gift 5").Visible = False
Sheets("Gift 6").Visible = False
Case "3"
Sheets("Gift 1").Visible = True
Sheets("Gift 2").Visible = True
Sheets("Gift 3").Visible = True
Sheets("Gift 4").Visible = False
Sheets("Gift 5").Visible = False
Sheets("Gift 6").Visible = False
Case "4"
Sheets("Gift 1").Visible = True
Sheets("Gift 2").Visible = True
Sheets("Gift 3").Visible = True
Sheets("Gift 4").Visible = True
Sheets("Gift 5").Visible = False
Sheets("Gift 6").Visible = False
Case "5"
Sheets("Gift 1").Visible = True
Sheets("Gift 2").Visible = True
Sheets("Gift 3").Visible = True
Sheets("Gift 4").Visible = True
Sheets("Gift 5").Visible = True
Sheets("Gift 6").Visible = False
Case "6"
Sheets("Gift 1").Visible = True
Sheets("Gift 2").Visible = True
Sheets("Gift 3").Visible = True
Sheets("Gift 4").Visible = True
Sheets("Gift 5").Visible = True
Sheets("Gift 6").Visible = True
End Select
End Sub

Thanks!
Pizza_Man
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Client Information" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in C16.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("C16")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Select Case Target.Value
        Case "Please Select"
            Sheets("Gift 1").Visible = False
            Sheets("Gift 2").Visible = False
            Sheets("Gift 3").Visible = False
            Sheets("Gift 4").Visible = False
            Sheets("Gift 5").Visible = False
            Sheets("Gift 6").Visible = False
        Case "1"
            Sheets("Gift 1").Visible = True
            Sheets("Gift 2").Visible = False
            Sheets("Gift 3").Visible = False
            Sheets("Gift 4").Visible = False
            Sheets("Gift 5").Visible = False
            Sheets("Gift 6").Visible = False
        Case "2"
            Sheets("Gift 1").Visible = True
            Sheets("Gift 2").Visible = True
            Sheets("Gift 3").Visible = False
            Sheets("Gift 4").Visible = False
            Sheets("Gift 5").Visible = False
            Sheets("Gift 6").Visible = False
        Case "3"
            Sheets("Gift 1").Visible = True
            Sheets("Gift 2").Visible = True
            Sheets("Gift 3").Visible = True
            Sheets("Gift 4").Visible = False
            Sheets("Gift 5").Visible = False
            Sheets("Gift 6").Visible = False
        Case "4"
            Sheets("Gift 1").Visible = True
            Sheets("Gift 2").Visible = True
            Sheets("Gift 3").Visible = True
            Sheets("Gift 4").Visible = True
            Sheets("Gift 5").Visible = False
            Sheets("Gift 6").Visible = False
        Case "5"
            Sheets("Gift 1").Visible = True
            Sheets("Gift 2").Visible = True
            Sheets("Gift 3").Visible = True
            Sheets("Gift 4").Visible = True
            Sheets("Gift 5").Visible = True
            Sheets("Gift 6").Visible = False
        Case "6"
            Sheets("Gift 1").Visible = True
            Sheets("Gift 2").Visible = True
            Sheets("Gift 3").Visible = True
            Sheets("Gift 4").Visible = True
            Sheets("Gift 5").Visible = True
            Sheets("Gift 6").Visible = True
    End Select
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi,
welcome to forum

Place following code in your worksheets "Client information" code page (right click tab > view code)

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i   As Long
    Dim rng As Range
    Set rng = Me.Range("C16")
    If Not Intersect(Target, rng) Is Nothing Then
     For i = 1 To 6
       Worksheets("Gift " & i).Visible = IsNumeric(rng.Value) And i <= rng.Value
     Next i
    End If
End Sub

Dave
 
Last edited:
Upvote 0
Solution
Both of these worked... thank you both! I really appreciate it!
 
Upvote 0
Glad we were able to help & appreciate the feedback

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
177
bobsan42
bobsan42
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
356
mumps
mumps
I
  • Question
Userform does not show leading 0 for telephone number
Replies
1
Views
87
ipbr21054
I
sparky2205
  • Question
ListBox not highlighting selection
Replies
0
Views
79
sparky2205
sparky2205
M
  • Question
Error #9
Replies
1
Views
228
BigBeachBananas
B

Forum statistics

Threads
1,215,741
Messages
6,126,610
Members
449,321
Latest member
syzer

Share this page

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
Back
Top