Using a combo to select sheets

brianv

Board Regular
Joined
Dec 11, 2003
Messages
100
I want to be able to goto a sheet when selected within a combo box. There are 6 sheets total.

I am using a Control Combobox and the named ranges are created, I just not sure how to write the code to look at the selection within the combobox.

I addition the combo resets to the first position "Select Sheet"

Any help..
BV
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

wouldn't rightclick on the arrowkeys at the bottomleft of your excelscreen be enough ?

else this might help
not sure why you are talking also about named ranges
Code:
Private Sub ComboBox1_Click()
ComboBox1.ListIndex = 1 'or 0 if you want empty
Sheets(ComboBox1.Text).Select
End Sub

Private Sub ComboBox1_GotFocus()
Dim sh As Worksheet
ComboBox1.Clear
For Each sh In Sheets
    ComboBox1.AddItem (sh.Name)
Next sh
End Sub

kind regards,
Erik
 

brianv

Board Regular
Joined
Dec 11, 2003
Messages
100
Ok, so I placed the code into the "Expenses" sheet objects, (modified) and inserted a control combobox named ComboSheet in "Expenses", the combobox dropps and lists the 6 sheets, but when selecting a sheet, it always switches to the 2nd tab (Not Sheet2, I define this because if I re-arange the tabs, it does not follow the sheet).

Also, upon the first select of the comboBox, the drop down menu indicates only 1 sheet, until I double-click it, then it displays all 6 sheets????

In further testing I now get:

Run-time error '9'
Subscript out of range

My 5 sheets are:
Expenses
Income
Rpt-Expenses
Rpt-Income
Catagories

My code is:
Private Sub ComboSheet_Click()
ComboSheet.ListIndex = 1 'or 0 if you want empty
Sheets(ComboSheet.Text).Select
End Sub

Private Sub ComboSheet_GotFocus()
Dim sh As Worksheet
ComboSheet.Clear
For Each sh In Sheets
ComboSheet.AddItem (sh.Name)
Next sh
End Sub


ideas???
BV
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
this is very simple code
if it doesn't work, I wonder what the circumstances of your workbook might be

your explanations are difficult to understand
where do you get the error "Subscript out of range " ? is this always occuring, what's the name of the sheet whne it bugs ?

Also, upon the first select of the comboBox, the drop down menu indicates only 1 sheet, until I double-click it, then it displays all 6 sheets????
when the combobx gets focus it is cleared and all sheetnames are loaded, that's very simple code
 

brianv

Board Regular
Joined
Dec 11, 2003
Messages
100

ADVERTISEMENT

Ok, I created a new newbook, "Book1" with 5 sheets (Sheet1-Sheet5). Empty in every way.

I then first copied my code listed above into the sheet1 objects. I then placed a control combobox on sheet1 named "ComboSheet".

Then, I selected the combobox, which 'opened' and displayed a single line "Sheet1" with what appeared to be a spin button on the right side. This spin button would allow scrollling between the sheets, or i could double-click on the combobox drop arrow which would then display all 5 sheets.

It would not matter which sheet I selected, the return again would always open the worksheet in the 2nd position of the workbook. Meaning if the sheet was ordered, Sheet1, Sheet3, Sheet2, Sheet4 & Sheet5, if I selected Sheet5 in the combobox then the workbook would open Sheet3.

I then copied the code to each sheets objects as well as inserted a combobox on every sheet all with the same name "ComboSheet". Then using the combobox on sheet1, no matter which sheet name selected, the return again would always open the worksheet in the 2nd position of the workbook. When using the combobox on sheet2-5, upon selection (click) it would open VB and give me a popup error window:
Microsoft Visual Basic
Run-time Error '9':

Subscript out of Range
END DEBUG


Thats it, no other info.

So I then altered the code to make it sheet specific, so for sheet1 it became ComboSheet1 and so forth all 5 sheet. Then in testing it reacted as above, no matter which sheet name selected, the return again would always open the worksheet in the 2nd position of the workbook

I'm confused...
BV[/i]
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
It would not matter which sheet I selected, the return again would always open the worksheet in the 2nd position of the workbook.
very descriptive post :)

sorry, I was wrong with some elements

you will be more happy with this
for some reason I inversed some lines
this is wrong, because the index is set to second item and then the sheet is selected
Code:
Private Sub ComboBox1_Click() 
ComboBox1.ListIndex = 1
Sheets(ComboBox1.Text).Select 
End Sub

now this will work
Code:
Option Explicit

Dim flag As Boolean

Private Sub ComboSheet_Click()

If flag Then Exit Sub

Sheets(ComboSheet.Text).Select

    flag = True
    ComboSheet.ListIndex = 0 '-1 if you want empty
    flag = False

End Sub

Private Sub ComboSheet_GotFocus()

Dim sh As Worksheet
ComboSheet.Clear

    For Each sh In Sheets
    ComboSheet.AddItem (sh.Name)
    Next sh

End Sub

getting further ?
best regards,
Erik
 

brianv

Board Regular
Joined
Dec 11, 2003
Messages
100
AAAAHHHHHH!! works like a dream, exactly what I needed...

Thansk

BV :biggrin:
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you're welcome !! :)

You probably saw this part of my first post and tried out...
wouldn't rightclick on the arrowkeys at the bottomleft of your excelscreen be enough ?
... it's good to avoid inventing the wheel if there are tools, but perhaps you had a good reason for the comboboxes...
 

Forum statistics

Threads
1,136,267
Messages
5,674,730
Members
419,523
Latest member
Urnovio

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
Top