PDA

View Full Version : Dropdown to change between spreadsheets



LBento
07-06-2012, 08:52 AM
Hi guys,

I have looked everywhere but nothing seems to work.

I am looking for a way to have a dropdown list that is populated with the name of the spreadsheets on the same workbook, so I can change spreadsheets jst by clicking in the dropbox. (Excel 2010)
I believe it requires VBA but I am starting my learning :D

Thank you

CodeNinja
07-06-2012, 09:11 AM
Add a combobox to each sheet and use the following code:

Private Sub ComboBox1_Change()
Dim s As String
s = ComboBox1.Value
If s <> "" Then Sheets(s).Select

End Sub

Private Sub Worksheet_Activate()
Dim i As Integer
Dim s As String
ComboBox1.Clear

For i = 1 To ThisWorkbook.Sheets.Count
s = ThisWorkbook.Sheets(i).Name
ComboBox1.AddItem (s)
Next i


End Sub
see attached as an example

8390

snb
07-06-2012, 09:26 AM
Excel contains a dropup in the left down corner that accomplishes what you are looking for.
Got to the browse buttons in the left down corner; then right mouse click;

LBento
07-06-2012, 10:24 AM
Thank you codeNinja, it works great.
The only problem is when I turn to return to Sheet 1, after choosing Sheet 2 from the dropdown it gives me and error.

snb, I am trying to avoid to have to use that function

"Run-time error '9':
Subscript out of range"´

Any ideas how to avoid it? Or just make the computer ignore the error?