Wrong type of Listbox from VBA

[Michael Singmin]

Hello group,

I am using this code to generate a listbox in a worksheet.
The only problem is that the listbox is from the Forms toolbar.
=====================================================================
Application.CommandBars("Control Toolbox").Visible = True
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False,
DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, Height:=125.25)
..Select
Application.CommandBars("Control Toolbox").Visible = False
======================================================================

I require the Linked Cell function of the Control Listbox .
I used the Macro record for this code.

Thanks,

Michael Singmin

 

[Dave Peterson]

Even though it says: Forms.listbox.1, it's from the control toolbox toolbar.

But this worked ok for me:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject

Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, _
Height:=125.25)

With OLEObj
.ListFillRange = ActiveSheet.Range("a1:a10").Address(external:=True)
.LinkedCell = ActiveSheet.Range("b1").Address(external:=True)
End With
End Sub

 

[Michael Singmin]

Well done Dave,

Again, I have learnt something new and valuable.
Much appreciated.

Michael
======================================================================

 

[Michael Singmin]

Hello again Dave,

I notice the listbox is not alive after I run this code. It does not
respond if I click it. What I have to do to wake it up, is to go
manually in and out of design mode (Control toolbar).

Also, what is the purpose of the (external:=true) in the code ?

Thanks,

Michael

 

[Dave Peterson]

Maybe you're in design mode.

Show that control toolbox toolbar and toggle the "design mode" icon. (Top left
corner of the toolbar.)

try a simple test:

msgbox range("a1").address
and
msgbox range("a1").address(external:=true)

You'll see that with external:=true, you get the workbook name, worksheet name
and cell address.

So I guess it's just one way of making sure that you're using the range you want
to use.

 

[Tom Ogilvy]

? ActiveSheet.Range("a1:a10").Address(external:=True)
[Book3]Sheet2!$A$1:$A$10
? activesheet.Range("A1:a10").Address
$A$1:$A$10