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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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