Adding Listbox to Excel via automation

[Rx_]

The Excel 2007 Recorder does not seem to register all of the parameters:

Help: need parameters to insert a list box on my Excel Worksheet - create a linked cell, and the ListRange

' this works - but leaves out the linked cell and ListFillRange
ObjXL.ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=209, Top:=36, Width:=170.25, Height:=25.5).Select

' this fails if the LinkedCell is added
ObjXL.ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, LinkedCell:="D1", _
DisplayAsIcon:=False, Left:=209, Top:=36, Width:=170.25, Height:=25.5).Select

'  ObjXL is an object reference to Excel
' ****   Add List box on sheet 1 and link it to the list just created on sheet 2 *****
' (Excel Recorder for reference) Left:=206.25, Top:=36, Width:=170.25, Height:=25.5).Select
 
' this works - but leaves out the linked cell and ListFillRange
ObjXL.ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=209, Top:=36, Width:=170.25, Height:=25.5).Select
 
' this  [B]fails when adding the Linked Cell[/B]
ObjXL.ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, LinkedCell:="D1", _
        DisplayAsIcon:=False, Left:=209, Top:=36, Width:=170.25, Height:=25.5).Select
 
' Found the following on Internet - but can't seem to get it to work either
' these values are the goal 
'With ObjXL.Worksheets(1).OLEObjects.Add("Forms.ComboBox.1", False, False, 206.25, 36, 170.25, 25.5)
        '.Name = "Status"
        '.Placement = 1
        '.LinkedCell = "D1"
        '[B].ListFillRange = "Sheet2!R9C2:R27C2"    ' will need this next[/B]
        'With ObjXL.Object
            '.BorderStyle = 1
            '.BorderColor = 0
            '.ShowDropButtonWhen = 1
            '.SpecialEffect = 0
            '.Text = "APD Approved"                      ' also will need 
        'End With
    'End With                                                  ' commented out while troubleshooting
' *************************************************************************************************************************************
ObjXL.Range("B8").Select   ' click off the list box

 

[Rx_]

Did not see any responses. Looked around the web and found things that did not work and one that did work. Thought I might post what worked for my Access 2007. Once in a while, the code at the listbox creation seems to crash. It will run three times, crase on the forth or fifth time.

Dim MyListFillRange As String
Dim MyText As String
MyListFillRange = "Sheet2!B9:B27"
MyText = "APD Approved"
With ObjXL.ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
        Link:=False, DisplayAsIcon:=False, _
        Left:=207, Top:=33, Width:=170, Height:=15)
        .LinkedCell = "C2"
 
        .ListFillRange = MyListFillRange
        '.Text = MyText   ' this caused an error
        With .Object
            .Text = MyText
        End With
End With

Now it looks so simple

This is a formula for Column C starting at Row 6. The last value in the formula is the LinkedCell above. Each column hading has auto filters.
This formula will count only the values in the list box and those that are visible from the filter.
=SUMPRODUCT(SUBTOTAL(3, OFFSET(C6:C5997, ROW(C6:C5997)-ROW(C6),0,1)),--(C6:C5997=C2))