Excel ComboBox 1

[JeffITman]

Does anyone know an easy way to create a combobox that switchs the sheet based upon selection in the dropdown?

Basically, I would like to have a workbook that contains a numerous amount of sheets and have a dropdown box on the top of everypage that allows the user to move to a sheet by clicking the dropdown box.

 

[SkipVought]

Hi,

As Gerry posted originally, in the Workbook_Open. Assuming that each sheet has a combobox1...

Private Sub Workbook_Open()
 for each ws1 in worksheets
   for each ws2 in worksheets
     if ws1.name <> ws2.name then _
       ws1.ComboBox1.AddItem ws2.name
   next
   ws1.ComboBox1.ListIndex = 0
 next
 
End With
End Sub
End Sub

Skip,

_[red][/red]

 

[JeffITman]

Ok. So i created the combobox on the sheet. Open the vb editor and place the code where? on change? Also, after the code has been placed, how do i make it where i can use the combo box.. all it does not is give me the option to edit it.

Thanks so much.

 

[JeffITman]

After i've created the list and it's working great I was tring to open VB Editor so I can do the same thing on the other pages, and it says "Project unviewable"... and clue?

 

[JeffITman]

Is there a way to load it on to the other sheets. Bascially, love what it does now with the box on sheet 1, but would like to have boxes on 2 and 3 as well to always have the ability to drop down a box to move to any of the sheets...

I used Gerry's code and it works great. I'm just not sure if I need to put a code in "thisworkbook" to make the combobox work properly on the other sheets.

 

[SkipVought]

The code, either mine or Jerrys', goes in the ThisWorkbook object code window.

It loads the ComboBox1 object on each sheet in the workbook when the workbook opens.

Skip,

_[red][/red]

 

[JeffITman]

Actually, yours populates all sheets. Gerry's does not. Thank you for your help! You guys are great!

 

[vladk]

JeffITman,

If you right click at the left bottom corner of the workbook (where the left/right arrows), you will get your combobox with all the sheets without any code.

vladk

 

[SkipVought]

vladk,

I can't count the number of times I have learned something NEW and USEFUL from my colleagues here at Tek-Tips!

Good TIP! ==> *

Skip,

_[red][/red]

 

[vladk]

Skip,

You are welcome. I can't count how many times your excellent answers helped me and thousands and thousands of other people around the world!

vladk

 

[JeffITman]

Skip,

I have the combo box working great. Do have a question for you though, when i drop the list to move to another page, the list works great. However, after I've moved to the page, the list defaults to the "home" option and doesn't give me the ability to move back to the home page without first moving to another page on the drop down list so "home" is not displayed.

Is there any way around this?

 

[SkipVought]

How 'bout this...

 For Each ws1 In Worksheets
   ws1.ComboBox1.Clear
   ws1.ComboBox1.AddItem ""
   For Each ws2 In Worksheets
     If ws1.Name <> ws2.Name Then _
       ws1.ComboBox1.AddItem ws2.Name
   Next
   ws1.ComboBox1.ListIndex = 0
 Next

Skip,

_[red][/red]

 

[JeffITman]

Script out of range... then it highlights Worksheets(ComboBox1.Value).Activate on my sheets...

 

[SkipVought]

The problem is with your code.

On each sheet object code sheet

Private Sub ComboBox1_Change()
    If ComboBox1.Value <> "" Then _
    Worksheets(ComboBox1.Value).Activate
End Sub

Private Sub Worksheet_Activate()
    ComboBox1.ListIndex = 0
End Sub

Skip,

_[red][/red]

 

[JeffITman]

Skip.. You are incredible with your coding.. Could you recommend any books to teach myself VB coding for excel and for windows applications? I've been trying to just teach myself but it is rather difficult. I read as many of these threads as possible... but I think a book or 20 may help a lot...

 

[SkipVought]

I like John Walkenbach's books. But check out others at a book store.

Use the macro recorder and observe. Experiment with customizing the code.

Read posts in sites like Tek-Tips. I STILL learn valuable things and I've been doing Excel VBA since 1994.

Tinker. Try stuff. Learn the Object Model. Use the Watch Window to discover object properties. Tinker more.

I can't tell you how many times I've "played around" with something somewhat academic (not directly related to my job) that became relevant to my job.

Skip,

_[red][/red]