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

Populate combo box in Excel

Experts,
How can one populate a combo box with the names of the spreadsheets in the workbook populating the combo box and then once selected go to that spreadsheet?  Do you use a form control or ActiveX control?
0
Frank Freese
Asked:
Frank Freese
  • 10
  • 3
  • 3
  • +1
3 Solutions
 
gtglonerCommented:
How about this as an example?
Book1.xls
0
 
Frank FreeseAuthor Commented:
take a look at your example - I think something is missing - thanks
0
 
ScriptAddictCommented:
I think that C2 is his Combo Box.  And pressing the button moves you there.  
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Frank FreeseAuthor Commented:
ok..that'll get me what I need. Why is the combox not visible?
0
 
gtglonerCommented:
I actually used a list box, it was easier to write some VBA code to do what you wanted to do with that.
0
 
NorieData ProcessorCommented:
It isn't a combobox, it's created via Data>Validation.

That should work fine though.

Or do you want/have a visible combobox?
0
 
Frank FreeseAuthor Commented:
ok...I use 2010 and save as 2003. Where's the list box in 2010?
0
 
Frank FreeseAuthor Commented:
can the control be made visible without clicking on cell C2 and how does one modify your list box?
0
 
Frank FreeseAuthor Commented:
a visible combo will be necessary
0
 
Frank FreeseAuthor Commented:
if we can get that combo visible we'll be ok
0
 
NorieData ProcessorCommented:
It isn't a combobox.

The attached workbook has a combobox that lists the worksheets.




EE-SheetNavCombo---17Oct2011.xlsm
0
 
Frank FreeseAuthor Commented:
hey...this looks good. I'll need to make a few cosmetic changes so now how did you do this?
0
 
NorieData ProcessorCommented:
What changes do you need to make?

You can right click the control and select Properties where you can set the Font, BackColour, ForeColour etc...
0
 
ScriptAddictCommented:
He went into design mode and added some vba code to the combo box controls.  Replace combobox1 with the name of the combo box you are using.  

To do this you must activiate design mode by using your developer tab on the ribbon.

Right click on your ribbon, and click customize ribbon.  

On the right hand side under main tabs click developer

Save and then click on the developer tab.

Activiate design mode by clicking on it.

Then if you right click on the control you should be able to see properties.  Click on that and the first property listed is the name of your combo box.  

Drop in the code below replacing combobox1 with the name of your combo box and your gold!

Hope that helps.

-Script Addict.

Option Explicit

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex <> -1 Then
    
        Application.Goto Worksheets(ComboBox1.Value).Range("A1"), True
        
    End If
End Sub

Private Sub Worksheet_Activate()
Dim ws As Worksheet

    ComboBox1.Clear
    
    For Each ws In ThisWorkbook.Worksheets
        ComboBox1.AddItem ws.Name
    Next ws
    
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
when I right click on the control the property window become disabled - not sure why.
0
 
ScriptAddictCommented:
Are you sure you have design mode activiated?  It should be highlighted yellow if you have that active.
0
 
Frank FreeseAuthor Commented:
missed clicking on design mode. I think I can take it from here...........many thanks
0
 
Frank FreeseAuthor Commented:
thanks to all - I do appreciate it!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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