Wrong type of Listbox from VBA
 

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

Wrong type of Listbox from VBA
 

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.ListB ox.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 wrote:

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

Wrong type of Listbox from VBA
 

Well done Dave,

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

Michael
================================================== ====================
Dave Peterson wrote:

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.ListB ox.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 wrote:

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

Wrong type of Listbox from VBA
 

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 wrote:

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.ListB ox.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 wrote:

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

Wrong type of Listbox from VBA
 

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.

Michael Singmin wrote:

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 wrote:

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.ListB ox.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 wrote:

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

Wrong type of Listbox from VBA
 

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

--
Regards,
Tom Ogilvy

"Michael Singmin" wrote in message
...
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 wrote:

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.ListB ox.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 wrote:

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