VBA - adding List box with linked cell and ListFillRange

**RxMiller** · 04-06-2011, 12:31 PM

The Excel 2007 Recorder does not seem to register all of the parameters:
Running this code in MS Access 2007

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  fails when adding the Linked Cell
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"
        '.ListFillRange = "Sheet2!R9C2:R27C2"    ' will need this next
        '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

**RxMiller** · 04-06-2011, 04:51 PM

Nobody responded, so I put it together after searching and tesing.

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

        With .Object
            .Text = MyText
        End With
End With

This formula relates to the LinkedCell above to change the formula.
ObjXL.Range("E2").Select
ObjXL.ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(SUBTOTAL(3, OFFSET(R[4]C[-1]:R[5995]C[-1], ROW(R[4]C[-1]:R[5995]C[-1])-ROW(R[4]C[-1]),0,1)),--(R[4]C[-1]:R[5995]C[-1]=RC[-1]))"

Thread: VBA - adding List box with linked cell and ListFillRange

Thread Tools

Rate This Thread

Display

VBA - adding List box with linked cell and ListFillRange

Re: VBA - adding List box with linked cell and ListFillRange

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions