Solved

Combo box of all worksheet names in a workgroup

Posted on 2001-06-16
12
272 Views
Last Modified: 2012-05-04
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
Comment
Question by:investfx
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 13

Expert Comment

by:cri
ID: 6197770
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
 

Author Comment

by:investfx
ID: 6197794
Thank you for your comment cri but I do need this in Visual Basic as I would plan to add more functionality.
0
 

Author Comment

by:investfx
ID: 6197795
Thank you for your comment cri but I do need this in Visual Basic as I would plan to add more functionality.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Accepted Solution

by:
a.marsh earned 150 total points
ID: 6197900
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
 
LVL 5

Expert Comment

by:jklmn
ID: 6198638
Hi investfx,

You need the combobox on a sheet or a userform?
0
 
LVL 5

Expert Comment

by:jklmn
ID: 6198660
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
 

Author Comment

by:investfx
ID: 6201797
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
 
LVL 15

Expert Comment

by:a.marsh
ID: 6201811
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
 
LVL 5

Expert Comment

by:jklmn
ID: 6201864
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
 

Author Comment

by:investfx
ID: 6201951
Thank you all for your help!

0
 
LVL 15

Expert Comment

by:a.marsh
ID: 6201976
Glad to help. :o)

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

:o\

Ant
0
 

Author Comment

by:investfx
ID: 6202118
Thank you all for your help!

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
Outlook Free & Paid Tools
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question