Combobox Changing Sheets - Setting Current Sheet As Selection In Combobox

[glerwell]

Hi

I'm using combobox's in all my sheets to navigate through my spreadsheet.

I'm using this macro assigned to each combobox:

Sub GotoComboBoxSelectedSheet()
With ActiveSheet.Shapes(Application.Caller).ControlFormat
On Error Resume Next
ThisWorkbook.Sheets(.List(.Value)).Activate
On Error GoTo 0
End With
End Sub

So when I select a sheet name in the combobox it switches to that sheet. When I switch to the sheet in question the combobx automatically displays the first sheet in the named rnage that populates the combobox.

So if I switch to "Sheet4", "Sheet1" is displayed in the combobox. Is there a way that the combobox could display the current sheet name as it selection?

Thanks

 

[xlnt]

If I'm understanding correctly, you want your combobox to populate with a list of the sheets in the workbook, you'll have a combobox on each sheet which will function identically, when you select a sheet name in the combobox, the workbook will switch to that sheet.

If this is the case, the object you are using has some requirements. A "Shape" ComboBox must have a location to pull a list from - it doesn't populate names with having a range reference like "A1:A10". You could accomplish your task with a UserForm ComboBox or with an ActiveX Control, but the code would be different.

Have I understood you correctly?

 

[glerwell]

The combobox which is a form control, is already populated with the sheet names and doing what I want it to do.

I was just wondering if the active sheet name could be displayed in the combobox or would I have to make do with it as it is. That is with the first sheet displayed in the combobox.

cheers

 

[sujon]

I also need to know that.