Visible based on value

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188
Can anyone tell me how to make a sheet hidden or unhidden based on the value in cell A!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
In VBA code, you can say:

Code:
sub test
if [A1] = 1 then
sheets("MySheet").visible = false
elseif [A1] = 0 then
sheets("MySheet").visible = true
end if
end sub

So if there is the value 1 in cell A1 then the sheet called MySheet will become hidden. If there is a 0 in the cell then it will be unhidden.

You can use this as an event to eliminate the need to run this macro everytime the value changes. If thats what you're after let me know and i'll adapt it for you.

HTH
 

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188
my spread sheet is made up of 13 worksheets. In the first worksheet named "ask ! " I have a drop down box that lets the user choose a year. I want only the Worksheet that represents that year to be visable, hope this makes sence
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Okay

So you need the combo box changing to trigger an event. As such you need to assign a macro to the combo boxes 'On Change' event. You can right click on teh combo box and select view code - by default it should bring you into its change event.

I need to know what relation the sheet names have to the values in this combo box. For example - if you have a sheets called "2002", "2003" and "2004", is the text in the combo representative of that (e.g. 2002, 2003, 2004) - or is it a relative affair - for example the combo box could say ("The First Year", "The Second Year" etc.)

I need to know this so that we can implement the sheet hiding effectively.

Other considerations include a workbook open event to ensure all the sheets are hidden in the first place (so that we can unhide the one that the user selects from the combo box), and we also need to make sure that when the user selects something from the combo box, the other sheet they viewed previously is hidden again. Do you wish the user to only ever have one sheet visible at a time, or can they make several visible dependent upon what they choose for the combo box

If you give me some answers to these questions I should be able to patch something together for you that will do the job.

Patrick
 

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188

ADVERTISEMENT

Sorry if I have mislead you but the dropdown box was created using data validation not a combo box will this make any difference ?
The sheets are named "op's 06-07" "op's 07-08" ect and tl's 06-07" "tl's 07-08 ect . I want the user to see only one sheet
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345

ADVERTISEMENT

Okay, still a little unclear about some stuff:

The data validation list is fine - makes things easier in fact.

Am I right in assuming the sheets are called 'op's - 06-07', etc?

Am I right in assuming the items in the drop down box list are called 'tl's 07-08' etc.?

What cell on the main sheet is the users selection displayed in?

What is the name of the first sheet displaying years data, and what is the name of the last sheet displaying years data? Is there only one sheet per year?
 

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188
Sorry for the confusiion. There are six sheets for Op's and six sheets for Tl's The list in the dropdown box is Jan 06-07, Jan 07-08, Jan 08-09 ect. The first sheet displaying years data is called OP'S 06-07 and the last sheet displaying years is called TL'S 11-12. There are two sheets per year one named OP'S & one named TL's
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Okay... untested here goes:

Code:
dim s as object
for each s in Sheets
if ucase(s.name) <> "ASK ! " then
s.visible = false
end if
next s

' all sheets are now hidden aside from master sheet

' presumes cell with drop down box is A1 in master sheet

dim what_we_want as string
what_we_want = Sheets("Ask ! ").[A1].text

what_we_want = mid(what_we_want,5,5)

for each s in sheets
if s.name = "OP'S " & what_we_want then or s.name = "TL'S " & what_we_want then
s.visible = true
end if
next s

Sheets("OP'S " & what_we_want).select

Made a couple of assumptions here:

1) That your main sheet is called "Ask ! " (including the trailing space) - you need to change that in the code if this is not the case.

2) That the data validation list is housed in cell A1 on that sheet - you need to change it in the code to reflect where we are picking that value up from.



Give it a try on your scenario and let me know any errors that occur. You will need to place a command button on the sheet that the user will click to make the code run. To do this, in XL goto View --> Toolbars --> Forms, and select the command button. It will ask you to assign a macro. You need to assign the macro i have put above. Then change the data validation list and hit the button (after making teh changes i suggest above, if necessary).

It should then work, but let me know because it is untested.
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
scrap that: use this code instead - slightly more tested now:

Code:
Sub Button1_Click()
Dim s As Object
For Each s In Sheets
If UCase(s.Name) <> "ASK ! " Then
s.Visible = False
End If
Next s

Dim what_we_want As String
what_we_want = Sheets("Ask ! ").[A1].Text

what_we_want = Mid(what_we_want, 5, 5)

For Each s In Sheets
If s.Name = "OP'S " & what_we_want Or s.Name = "TL'S " & what_we_want Then
s.Visible = True
End If
Next s

Sheets("OP'S " & what_we_want).Select

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,916
Members
410,711
Latest member
Josh324
Top