[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Combo box of all worksheet names in a workgroup

Posted on 2001-06-16
12
Medium Priority
?
279 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
Independent Software Vendors: 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 450 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

834 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