excel 2007 vba add item sheet names to listbox

Question

Using Excel 2007 VBA

Have a sheet "Dashboard", have other sheets but some special task sheets that can be anywhere in the book, but will always be found between Sheets "TaskNew" and "TaskEnd". These special task sheets will vary in count.

Upon dashboard activation, want to populate the listbox with the names of all special task sheets, so that user can select a sheet form drop down and navigate to that sheet.

Following results in runtime error '438' Object doesn't support this property or method:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Dim StartIndex As Integer
Dim EndIndex As Integer
Dim LoopIndex As Integer
'Dim sht As Worksheet
StartIndex = Sheets("TaskNew").Index + 1
EndIndex = Sheets("TaskEnd").Index - 1
For LoopIndex = StartIndex To EndIndex
    'SheetsNavigationDropDown.AddItem Sheets(LoopIndex).Name
    'Sheets("Dashboard").ListBox20.AddItem Sheets("Sample").Name
    Sheets("Dashboard").ListBox20.AddItem Sheets(LoopIndex).Name
Next LoopIndex

I have tried it also as just ListBox20.AddItem ... but still doesn't work. Yes, ListBox20 exists on Sheets("Dashboard"), yes I edited it's name to eliminate spaces.

It chokes on/highlights the Sheets("Dashboard").List.... line when debugging.

Ideas?

Answer 1

Might as well start with a sanity check:

Dim o As OLEObject

Debug.Print "---"
For Each o In Sheets("Dashboard").OLEObjects
    Debug.Print o.Name & " = " & o.progID
Next o
Debug.Print "---"

and you should get ListBox20 = Forms.Listbox.1 as one of the results

(edit: this works in Excel 2003 anyway)

Answer 2

Sorry, answered my own question, key was to switch to an activex form element instead of regular.

Sheets("Dashboard").TaskSheetsComboBox.Clear
Dim StartIndex As Integer
Dim EndIndex As Integer
Dim LoopIndex As Integer
StartIndex = Sheets("TaskNew").Index + 1
EndIndex = Sheets("TaskEnd").Index - 1
For LoopIndex = StartIndex To EndIndex
    Sheets("Dashboard").TaskSheetsComboBox.AddItem Sheets(LoopIndex).Name
Next LoopIndex

Private Sub TaskSheetsComboBox_Click()
    Sheets(Sheets("TaskNew").Index + TaskSheetsComboBox.ListIndex + 1).Activate
End Sub