PDA

View Full Version : Sleeper: Excel Hide/Unhide Sheets based on cell value



kungpowell
02-21-2012, 07:45 PM
Hello all,
Newby here, looking to create a macro that based on the value of a cell it hides and unhides other sheets in the same workbook.

My workbook has a worksheets called "main", "instructions", "sheet 1", "sheet 2", "sheet 3", "sheet 4" and "sheet 5". What I would like to setup is that on the main page it shows the choices and has a cell for you to enter a number and based on the number that is entered in (1, 2, 3, 4 or 5), I would like all the sheets to hide except the instructions, the main page, and the sheet with the corresponding number.

Example, on the main page if I enter into the cell "1" it will only show the sheets "main", "instructions", and "sheet 1". Or if I enter into the cell "3" it will only show the sheets "main", "instructions", and "sheet 3"

Thank you all in advance!

GTO
02-21-2012, 08:27 PM
Greetings and welcome to vbaexpress:hi:

In the worksheet's module for "main", try:



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SheetName As String
Dim wks As Worksheet
If Not Application.Intersect(Target, Range("A2")) Is Nothing And Target.Count = 1 Then
If IsNumeric(Range("A2").Value) And Not Range("A2").Value = vbNullString Then
Select Case Range("A2").Value
Case 1 To 5
For Each wks In ThisWorkbook.Worksheets
If Not wks.Name = "main" And Not wks.Name = "instructions" And Not wks.Name = "sheet " & Range("A2").Value Then
wks.Visible = xlSheetVeryHidden
Else
wks.Visible = xlSheetVisible
End If
Next
Case Else
MsgBox "Value must be between 1 and 5 (inclusive)", 0, vbNullString
End Select
End If
End If
End Sub


Does that help?

Mark

kungpowell
02-21-2012, 08:36 PM
Mark, thank you very much for you answers, it does help, but I am afraid I over simplified the problem.

Attached is a copy of the workbook in question, the main page is "Products Sold" and give the options for four sets of products that will correspond to an order sheet. In that sheet it gives you the option to enter an 'X' into the products you want on the order form (I don't have any of the order forms formatted, at this point, but have place hold sheet names in). On the Products Sold sheet the yellow columns are where the 'X' is to be entered, based on that it developed a code in E6, that number corresponds to a Order Form ____ like you can see on the other sheet names. All of the orders will be required to have B2 selected. So if only B2 is selected I would like only "Order Form 0001" and "Products Sold" open. Where as if I have B2 and B5 selected it will only show "Order Form 1001" and the "Products Sold" page.

Please let me know if this makes sense.

Thanks in advance.

GTO
02-23-2012, 12:05 AM
Mark, thank you very much for you answers, it does help, but I am afraid I over simplified the problem.

Attached is a copy of the workbook in question, the main page is "Products Sold" and give the options for four sets of products that will correspond to an order sheet. In that sheet it gives you the option to enter an 'X' into the products you want on the order form (I don't have any of the order forms formatted, at this point, but have place hold sheet names in).

On the Products Sold sheet the yellow columns are where the 'X' is to be entered, based on that it developed a code in E6, that number corresponds to a Order Form ____ like you can see on the other sheet names. All of the orders will be required to have B2 selected. So if only B2 is selected I would like only "Order Form 0001" and "Products Sold" open. Where as if I have B2 and B5 selected it will only show "Order Form 1001" and the "Products Sold" page.

Please let me know if this makes sense.

Thanks in advance.

Hi again,

I admit that I read this quickly, but I am not exactly understanding what it is you want to do.

Mark

kungpowell
02-23-2012, 02:26 PM
Hey Mark, sorry it was probably a little confusing...

Looking at the file I attached, on the sheet called "Products Sold" there is a number generated in cell E6, that is four digits based on where the Xs are placed in cells B2:B5. This code corresponds to a specific form, there are 8 different forms, each is a sheet in the workbook. For example is the code that is created is "0011" in cell E6 then the correct form to use is the sheet called "Order Form 0011". What I would like to setup is based on the number in cell E6, only the correct sheet is visable, the other order forms become hidden. I would always like for the "Products Sold" and the "Products-Price" to always be visable.

This list shows the possible codes and what sheet needs to be the only order form visable.
Code (E6) -->Order Form Visable
0001 Order Form 0001
0011 Order Form 0011
0101 Order Form 0101
1001 Order Form 1001
0111 Order Form 0111
1011 Order Form 1011
1101 Order Form 1101
1111 Order Form 1111

Please let me know if this makes sense...

Thanks Mark!

GTO
02-25-2012, 01:48 PM
Hi again,

Sorry for the slow response. Given the question as I understand it, try:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
If Not Application.Intersect(Target, Range("B2:B5")) Is Nothing And Target.Count = 1 Then
If IsNumeric(Range("E6").Value) And Not Range("E6").Value = vbNullString Then
'Order Form 0001
Select Case Range("E6").Value
Case 1, 11, 101, 111, 1001, 1011, 1101, 1111
For Each wks In ThisWorkbook.Worksheets
If Not wks.Name = "Products Sold" _
And Not wks.Name = "Products-Price" _
And Not wks.Name = "Order Form " & Format(Range("E6").Value, "0000") Then
wks.Visible = xlSheetVeryHidden
Else
wks.Visible = xlSheetVisible
End If
Next
Case Else
MsgBox "Unacceptable product combination", 0, vbNullString
End Select
End If
End If
End Sub

I would note however, at least as I count, there are fifteen different combinations possible. Your order forms would cover 1, 11, 101, 111, 1001, 1011, 1101, and 1111. Missing are: 10, 100, 110, 1000, 1010, 1100, and 1110. If there are only eight acceptable combinations of product(s), I might consider a drop-down instead.