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
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.
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
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
dim s as object
for each s in Sheets
if ucase(s.name) <> "ASK ! " then
s.visible = false
' 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
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.
scrap that: use this code instead - slightly more tested now:
Dim s As Object
For Each s In Sheets
If UCase(s.Name) <> "ASK ! " Then
s.Visible = False
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
Sheets("OP'S " & what_we_want).Select