combo box not populating

ok...nothing changed

Would you like to volunteer any more information, or post a workbook? Otherwise we're not going to get very far.

sure....the workbook has nothing in it except rest data anyway. see attached
Weekly-Cash-Flow-Projection.xlsm

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

Select allOpen in new window

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.

You need to remove it from everywhere except the TW modue.

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

 

Select allOpen in new window

Where I have I gone wrong?

Have you followed my instructions?

The code above is what I did....so I think I did. so If not, how do I correct this, please?

I gues I don't know how to create a workbook module? Please advise

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

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

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

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....

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

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?

woops! my fault. placed code in wrong event. by the way, great link....thank you. everything's ok!

thank you