combo box not populating

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

Frank FreeseAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
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
 
StephenJRConnect With a Mentor Commented:
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
 
Frank FreeseAuthor Commented:
ok...nothing changed
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
StephenJRCommented:
Would you like to volunteer any more information, or post a workbook? Otherwise we're not going to get very far.
0
 
Frank FreeseAuthor Commented:
sure....the workbook has nothing in it except rest data anyway. see attached
Weekly-Cash-Flow-Projection.xlsm
0
 
StephenJRCommented:
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
 
Frank FreeseAuthor Commented:
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
 
StephenJRCommented:
You need to remove it from everywhere except the TW modue.
0
 
Frank FreeseAuthor Commented:

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
 
StephenJRCommented:
Have you followed my instructions?
0
 
Frank FreeseAuthor Commented:
The code above is what I did....so I think I did. so If not, how do I correct this, please?
0
 
Frank FreeseAuthor Commented:
I gues I don't know how to create a workbook module? Please advise
0
 
Frank FreeseAuthor Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
Frank FreeseAuthor Commented:
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
 
dlmilleCommented:
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
 
Frank FreeseAuthor Commented:
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
 
Frank FreeseAuthor Commented:
woops! my fault. placed code in wrong event. by the way, great link....thank you. everything's ok!
0
 
Frank FreeseAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.