[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Combo box of all worksheet names in a workgroup

Posted on 2001-06-16
12
Medium Priority
?
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
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…

650 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