Combo box of all worksheet names in a workgroup

Hi all,

I want to create a list box or combobox preferrably with the titles of the other worksheets in a workbook. There are many worksheets, and when I create a new one, I ould like the list to update.
Also on mouse click of a particular worksheet name in the list I need the user to be able to go to that worksheet.

I would appreciate some code for this as I have little VB or excel programming skills.
Thanks.
 
investfxAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
a.marshConnect With a Mentor Commented:
I have done VERY little with VB...but I've manager to put this together - is it what you are looking for?

http://www.btinternet.com/~ant.marsh/ee/sheetlist.xls

:o)

Ant
0
 
criCommented:
Excel has this feature already: Right click the |< symbol in the bottom left corner of the Sheet-tabs bar.

If you need this in VBA please wait for another expert, I try to stay clear of Forms (because of ActiveX)
0
 
investfxAuthor Commented:
Thank you for your comment cri but I do need this in Visual Basic as I would plan to add more functionality.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
investfxAuthor Commented:
Thank you for your comment cri but I do need this in Visual Basic as I would plan to add more functionality.
0
 
jklmnCommented:
Hi investfx,

You need the combobox on a sheet or a userform?
0
 
jklmnCommented:
This code can
1. lists all sheet names in the combobox when open the file.
2. update the list when adding a new sheet, and
3. activate a sheet according the combobox item clicked.

Put ComboBox1 and following Sub in Sheet2:
Private Sub ComboBox1_Click()
Sheets(ComboBox1.Text).Activate
End Sub

Put following Subs in ThisWorkBook:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sheet2.ComboBox1.AddItem Sh.Name
End Sub
Private Sub Workbook_Open()
For Each aSheet In Sheets
Sheet2.ComboBox1.AddItem aSheet.Name
Next
End Sub
0
 
investfxAuthor Commented:
Hi jklmn,

Are you sure this code is correct. I cannot get
it to work.
I am using it with the combobox on a form.
Thanks.
0
 
a.marshCommented:
Have you looked at my example?!?!?

I really do not like giving time and effort and then being ignored.......(because that is what it seems like!)

:o\

Ant
0
 
jklmnCommented:
Hi investfx,

1. Have your tried a.marsh's link?
2. Yes, I checked my code before posting. It works in Excel2000 in my PC. So, can you tell me what is worng?
3. My code uses combobox in Sheet2, not in userform.

0
 
investfxAuthor Commented:
Thank you all for your help!

0
 
a.marshCommented:
Glad to help. :o)

...........but a B grade! What more did you want for an A???

:o\

Ant
0
 
investfxAuthor Commented:
Thank you all for your help!

0
All Courses

From novice to tech pro — start learning today.