Show/Hide Specific (Changeable) Sheets based on Cell Value

[justanotheruser]

Hi guys,

I've been searching forums to no end to try to find a solution to this, but I've hit a road block.

I have a sheet called "Title" and cell E4 in this sheet is a drop down list which allows you to select certain codes, for example 210, 220 or 230.

Based on values in this cell, I would like to show certain worksheets, which are defined in another sheet, "Mapping", that contains the following values in Column C and D respectively.

Code Sheet Name Shown
210 PL_Workwear
210 BS_Workwear
220 PL_Cleanroom
220 BS_Cleanroom

I've managed to do this if I just enter the sheet names in the VBA code myself, but unfortunately it needs to be done so that a user who doesn't know how to use VBA can add more sheets and sheet names that will be shown by simply adding a line to the Mapping sheet, so they could add the line:

210 MM_Workwear

To the mapping sheet, and then if they select code 210 from the drop down box in Cell E4 in the Title sheet, then PL_Workwear, BS_Workwear and MM_Workwear will need to be shown.

My VBA isn't good enough to know how to do this - use dynamic ranges or VLOOKUPs with more than one value like on http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx#_Toc273640147?

Any help or code you could provide would be very much appreciated!

 

[justanotheruser]

The code I've tried which works for a CheckBox but it's static code that would have to be edited is:

Private Sub CheckBox1_Click()
     'Code for the Check Box to access hidden pages
    If CheckBox1.Value Then
        Sheet3.Visible = xlSheetVisible
        Sheet6.Visible = xlSheetVisible
         'If box is checked then sheet 3 is visible
    Else
        Sheet3.Visible = xlSheetVeryHidden
        Sheet6.Visible = xlSheetVeryHidden
         ' if it is not checked then sheet 3 is very hidden
         ' All other sheets are very hidden
    End If
End Sub

I don't know if this would help but I think it might be too basic to be of much use...

 

[VoG]

Try this: right click the Title sheet's tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "E4" Then
    Select Case Target.Value
        Case 210: Sheets("PL_Workwear").Visible = True: Sheets("BS_Workwear").Visible = True
        Case 220: Sheets("PL_Cleanroom").Visible = True: Sheets("BS_Cleanroom ").Visible = True
        Case Else: ' maybe hide them all
    End Select
End If
End Sub

You can add more Cases as needed.

 

[justanotheruser]

Hi VoG,

Thanks for your response, when I change to 220 from the dropdown list, I get Run-time error 9: Subscript out of range.

The other major problem is that I would love to be able to just add the sheet values in the VBA code but the problem is the sheet has to be made such that the end user (not me) needs to be able to add more sheets to show for 210 for example, so they would add "MM_Workwear" to the Mapping sheet and then when they select from the drop down on the Title sheet, this will update by itself, without the end user having to update or change any VBA code.

Is this possible?

Thanks again!

 

[VoG]

That error would mean that the sheet it is trying to unhide doesn't exist (or I've mis-spelt the name).

I don't know how to do what you want without hard coding all the codes and sheet names.

 

[justanotheruser]

Hi,

So the only way to do it would be to hard code it into the macro code - it can't be defined by linking to named ranges/cells in the Mapping sheet?

 

[VoG]

You would need to hard code it either in the macro or with a lookup table on a sheet. The code would then need to be completely changed to do a VLOOKUP.

 

[justanotheruser]

The VLookup is what I don't know how to do within the VBA code, the mapping table in the mapping sheet is as follows:

Excel Workbook
	C	D
2	Entity	Sheet Name
3	210	PL_Workwear
4	210	BS_Workwear
5	220	PL_Cleanroom
6	220	BS_Cleanroom
Mapping

Would it be possible to either set the range for the VLookup to many rows down (so when more are added there are no problems) or set the range as dynamic so it fixes itself?

Thanks again!

 

[VoG]

Try like this but note that as VLOOKUP only returns the first match it will only unhide one sheet. An alternative would be a loop but are the codes and sheet names always in pairs?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
If Target.Address(False, False) = "E4" Then
    s = WorksheetFunction.VLookup(Target.Value, Sheets("Mapping").Range("C3:D100"), 2, False)
    Sheets(s).Visible = True
End If
End Sub

 

[justanotheruser]

Yes. The codes and sheet names will always be together, but one sheet can be opened up by more than one code - so I don't know if a loop would work, for example:

Excel Workbook
	C	D
2	Entity	Sheet Name
3	210	PL_Workwear
4	210	BS_Workwear
5	210	PL_Cleanroom
6	210	BS_Cleanroom
7	220	PL_Workwear
8	220	BS_Workwear
9	230	PL_Cleanroom
10	230	BS_Cleanroom
11	230	PL_Workwear
12	230	BS_Workwear
Mapping

For the VLOOKUP only returning the first match, I found this microsoft page - but I don't understand how to configure it that well