ActiveX ComboBox To Automatically Update with Worksheet Names

[MelindaMartin]

I have a combox box that allows me to select a worksheet name from a drop down list and then it takes me to that worksheet. That part is awesome.

It works in conjunction with a command button. The command button generates the combobox with the worksheet names. This part is not awesome.

My goal is to have the combobox automatically update with the workbook's current worksheets and to not have a command button at all--just to have the combobox.

The combobox code is:

Private Sub ComboBox1_Change()
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Sheets(ComboBox1.Value)
ws.Select


On Error GoTo 0
End Sub

The command box code is:

Private Sub CommandButton1_Click()
Dim n As Long
For n = 1 To ActiveWorkbook.Sheets.Count
ComboBox1.AddItem ActiveWorkbook.Sheets.Name
Next n
End Sub

How do I get the combobox to automatically update with any new and/or deleted worksheet names?

TIA

 

[My Aswer Is This]

Try this:

Private Sub ComboBox1_Click()
Sheets(ComboBox1.Value).Activate
End Sub
Private Sub ComboBox1_GotFocus()
Dim i As Long
ComboBox1.Clear
For i = 1 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
End Sub

OK

 

[MelindaMartin]

Worked Great! And in the short time I was waiting, i also got it to work in a user form via a different method.

Thanks so much! I didn't want to have to go to a form, and this is perfect.

Try this:

Private Sub ComboBox1_Click()
Sheets(ComboBox1.Value).Activate
End Sub
Private Sub ComboBox1_GotFocus()
Dim i As Long
ComboBox1.Clear
For i = 1 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
End Sub

OK

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Worked Great! And in the short time I was waiting, i also got it to work in a user form via a different method.

Thanks so much! I didn't want to have to go to a form, and this is perfect.

 

[MelindaMartin]

I spend my day helping people in the self-publishing industry and run a forum of my own, so it's nice to get to play the damsel in distress for a change, lol. Knowledge is power, and I appreciate you sharing your power with me.

 

[My Aswer Is This]

I like helping others and learning more every day myself. Excel can do wonders.

I spend my day helping people in the self-publishing industry and run a forum of my own, so it's nice to get to play the damsel in distress for a change, lol. Knowledge is power, and I appreciate you sharing your power with me.

 

[MelindaMartin]

The only other question I have on this is:

The drop-down only lists one tab at a time and then there are mini-scroll bars inside the drop-down list on that single worksheet name line. How do I make it not have those mini-scrollbars?

https://www.dropbox.com/s/5764n5tzs1o5zyz/Screenshot 2016-12-19 21.45.18.png?dl=0

 

[My Aswer Is This]

I see what your referring to. Not sure why that is happening.
Try these scripts:

To load the combobox with values you will need to activate the sheet.
Activate means you need to go to another sheet and then come back to this sheet. But only once unless you add more sheets then you will have to activate the sheet with the combobox again.

Private Sub ComboBox1_Click()
Sheets(ComboBox1.Value).Activate
End Sub
Private Sub Worksheet_Activate()
Dim i As Long
ComboBox1.Clear
    For i = 1 To Sheets.Count
        ComboBox1.AddItem Sheets(i).Name
    Next
End Sub

OK

 

[My Aswer Is This]

Or try this:

To load the combobox with new sheet names doubleclick on the combobox

Private Sub ComboBox1_Click()
Sheets(ComboBox1.Value).Activate
End Sub
Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
ComboBox1.Clear
    For i = 1 To Sheets.Count
        ComboBox1.AddItem Sheets(i).Name
    Next
End Sub