Select Graph with a Combo Box

I currently have a spreadsheet with about 30 graphs, and when I am done there is going to be about double that.

I want to make one sheet, with a combo box that allows me to select which graph to display, so that users don't have to cycle through a large number of tabs.

I have seen this done before, but can't figure out how to do it. The extent of what I know is making a combo box and selecting the data range for the options to select in the combo box.

Any help?

can you clarify, are all the charts based on the same x/y data for each person or is the data specific to each person.

cheers reg

The data is all over the place and each chart is a little different than the other. I'm looking at expenses across districts and conducting various kinds of comparisons and analysis. So I have about 30 tabs, each with its own already created chart.

Maybe if there is a way to to put them all on one sheet, hide them, then have a drop down box that makes the one I select come up to the top of the page and appear.

Did that clarify? Or did you need something else?

Bump, still looking for help.

would it be possible to post an example of what you need,

thanks reg

Unfortunately not because it is sensitive company data.

I have 40 graphs. Instead of having a user look at the tabs on the bottom of the excel workbook to select a graph, I would like them to be able to open up one tab (one sheet). On that sheet I would like to have a drop down box that lists the titles for each of the 40 charts. The user then simply selects the chart they want to view, and it pops right up.

Then if they want to look at a different chart, they select another option in the drop down box, and the first chart disappears and the newly selected one appears.

hi Tnesper,

there is no quick way to hyperlink to tabs in a workbook, but there is a workaround, firstly type all 40 worksheets onto the first page in a list from say A1 down, then in each of the cells Right mouse click and select hyperlink and click "place in this document" and select the correct worksheet and click OK, then using data validation select all the cells so that a drop dwon list appears, then copy the following code

Macro to Use Hyperlinks in Validation List

Please Login or Register  to view this content.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Addx As String
Dim Cell As Range
Dim Rng As Range
Dim VLO As Validation

With Target
If .Validation.Type = xlValidateList Then
Set VLO = .Validation
Addx = IIf(Left(VLO.Formula1, 1) = "=", Mid(VLO.Formula1, 2), VLO.Formula1)
On Error Resume Next
Set Rng = Range(Addx)
If Err = 1004 Then
Err.Clear
On Error GoTo 0
Set Rng = ThisWorkbook.Names(Addx).RefersToRange
End If
For Each Cell In Rng
If Cell.Value = Target.Value Then
If Cell.Hyperlinks.Count <> 0 Then
Cell.Hyperlinks(1).Follow
End If
End If
Next Cell
End If
End With


End Sub

Please Login or Register  to view this content.

How to save the Worksheet Event Macro

1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Save the macro in your Workbook using CTRL+S


this will allow you to select the worksheet name in the drop down list and will go straight to that particular sheet, if you need to go back to the start, then place a hyperlink in cell A1 back to the start page.

i have attached a small example

hope it helps

reg

For some reason the hyperlink list is only showing the sheets that have the data and not the sheets that contain the charts. If this is getting to out of hand then I would just assume forget it.

I got that to work with one cell. But because I don't know anything about coding in excel, I don't know how to enter it into there for multiple cells going down the column. Could you just paste how I would enter it so it will do it for Cells B2 and B3 (or any cells) and I can hopefully take it from there.