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
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.