Using a combo to select sheets

[brianv]

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

 

[erik.van.geit]

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

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]

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]

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]

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]

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

Private Sub ComboBox1_Click() 
ComboBox1.ListIndex = 1
Sheets(ComboBox1.Text).Select 
End Sub

now this will work

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]

AAAAHHHHHH!! works like a dream, exactly what I needed...

Thansk

BV

 

[erik.van.geit]

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...