?
Solved

combo box not populating

Posted on 2011-10-18
20
Medium Priority
?
194 Views
Last Modified: 2012-05-12
Experts,
When my workbook opens to my first worksheet I'm trying to populate a combo box. This only seems to work when I move to one worksheet and the return to the worksheet with the combo box. Would like to be able to populate the combo box when the workbook opens.
   
Private Sub Worksheet_Activate()
Dim ws As Worksheet
    cboSelectSpreadsheet.Clear
    For Each ws In ThisWorkbook.Worksheets
        cboSelectSpreadsheet.AddItem ws.Name
    Next ws
End Sub

Open in new window

0
Comment
Question by:Frank Freese
  • 11
  • 5
  • 4
20 Comments
 
LVL 24

Assisted Solution

by:StephenJR
StephenJR earned 400 total points
ID: 36987819
Put it in the workbook open then (double-click ThisWorkbook in the VB editor and paste):
Private Sub Workbook_Open()
    Dim ws As Worksheet
    cboSelectSpreadsheet.Clear
    For Each ws In ThisWorkbook.Worksheets
        cboSelectSpreadsheet.AddItem ws.Name
    Next ws
End Sub

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 36987988
ok...nothing changed
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36988524
Would you like to volunteer any more information, or post a workbook? Otherwise we're not going to get very far.
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!

 

Author Comment

by:Frank Freese
ID: 36988707
sure....the workbook has nothing in it except rest data anyway. see attached
Weekly-Cash-Flow-Projection.xlsm
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36988752
You need to put this code in the ThisWorkbook module, not a sheet module:
Private Sub Workbook_Open()
Dim ws As Worksheet
With Sheets("Home").OLEObjects("cboSelectSpreadsheet").Object
    .Clear
    For Each ws In ThisWorkbook.Worksheets
        .AddItem ws.Name
    Next ws
End With
End Sub

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 36989077
I did...same result - nothing listed in the combo box. When I simply select another tab and run the Home macro is the combo box populated.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36989084
You need to remove it from everywhere except the TW modue.
0
 

Author Comment

by:Frank Freese
ID: 36989270

This is all the code I have in that worksheet...
Option Explicit
Private Sub cboSelectSpreadsheet_Change()
  If cboSelectSpreadsheet.ListIndex <> -1 Then
    
        Application.Goto Worksheets(cboSelectSpreadsheet.Value).Range("A7"), True
        
    End If
End Sub
Private Sub Worksheet_Activate()
Dim ws As Worksheet

   cboSelectSpreadsheet.Clear
    
    For Each ws In ThisWorkbook.Worksheets
       If ws.Name <> "Home" Then cboSelectSpreadsheet.AddItem ws.Name
    Next ws
    
End Sub
Private Sub Workbook_Open()
Dim ws As Worksheet
With Sheets("Home").OLEObjects("cboSelectSpreadsheet").Object
    .Clear
    For Each ws In ThisWorkbook.Worksheets
        .AddItem ws.Name
    Next ws
End With
End Sub

 

Open in new window

Where I have I gone wrong?
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36989303
Have you followed my instructions?
0
 

Author Comment

by:Frank Freese
ID: 36989422
The code above is what I did....so I think I did. so If not, how do I correct this, please?
0
 

Author Comment

by:Frank Freese
ID: 36989485
I gues I don't know how to create a workbook module? Please advise
0
 

Author Comment

by:Frank Freese
ID: 36990107
ok....I pasted the code in ThisWookBook module. Save the changes and reopened the workbook. The combo box still is not populating when the wookbook first opens. Suggestions....
I attached the workbook as amended. Continued help is appreciated.
Weekly-Cash-Flow-Projection.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36990315
Change the sub Addnames() in your ThisWorkbook code module to:


Private Sub Workbook_Open()
'your code here
End Sub


So - in the ThisWorkbook code module, it should show only:

Private Sub Workbook_Open() 'not AddNames
Dim ws As Worksheet
With Sheets("Home").OLEObjects("cboSelectSpreadsheet").Object
    .Clear
    For Each ws In ThisWorkbook.Worksheets
        .AddItem ws.Name
    Next ws
End With
End Sub

See attached.

Cheers,

Dave
Weekly-Cash-Flow-Projection.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36990328
PS - and you can delete the workbook_Open event routine in the Home codepage.  That will never run there.  Only worksheet-related events will run there.

Workbook related events (run at a higher level) are triggered in the ThisWorkbook code page.

So - Sheet-related events go in specific sheet code pages, and Workbook-related events go in ThisWorkbook codepage.

You can trap sheet-related events at the higher level (in ThisWorkbook) but that's another question, and ask it when you're ready :)

Here's a primer on workbook/sheet events: http://www.cpearson.com/excel/Events.aspx

Its a tough read from top to bottom, unless you have a problem to solve - then use it as a great reference!

Dave
0
 

Author Comment

by:Frank Freese
ID: 36990608
question...is there any way that when the workbook opens it does not read into the combo box the worksheet titled "Home"? Outside of that, I appreciate the explanations....
0
 
LVL 42

Accepted Solution

by:
dlmille earned 1600 total points
ID: 36990616
Sure:

Sub workbook_open()
Dim ws As Worksheet
With Sheets("Home").OLEObjects("cboSelectSpreadsheet").Object
    .Clear
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Home" Then .AddItem ws.Name
    Next ws
End With
End Sub
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36990619
Nice start to the app, by the way.  Jumps right to the sheet you want to work on (and you can create a hyperlink on those sheets to take you back "Home")

:)

Dave
0
 

Author Comment

by:Frank Freese
ID: 36992577
thanks
I looked at the code for WorkbookOpen and you'd think that all ws except "Home" would populate the combo box - "Home" still shows up?
0
 

Author Comment

by:Frank Freese
ID: 36992729
woops! my fault. placed code in wrong event. by the way, great link....thank you. everything's ok!
0
 

Author Closing Comment

by:Frank Freese
ID: 36992751
thank you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

807 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