Solved

Combo box of all worksheet names in a workgroup

Posted on 2001-06-16
12
270 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PowerPoint Links 7 42
VBA Listbox: Mouseover to display full text of listbox items 4 52
Excel Spacing Anomaly 4 22
Access Open Report with SQL Parameter 11 29
Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

786 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