Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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.
 
0
investfx
Asked:
investfx
  • 5
  • 3
  • 3
  • +1
1 Solution
 
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
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
a.marshCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now