Adding a Controls Listbox via VBA

[Michael Singmin]

Hello group,

I want to insert a Controls listbox onto a sheet (VBA).

I switched on macro record and this is what came out.
"ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False,
DisplayAsIcon:=False, Left:=285, Top:=141, Width:=77.25,
Height:=118.5).Select"

This seems to be a strange construct.
If I try an step through with F8, I get this error message
"Can't enter breakmode at this time"
with 3 buttons "Continue","End","Help"

If I run it normally with F5 then it creates the listbox.
How do I find the name of the listbox just created?
It could be listbox1,listbox2.listbox3 etc

Thanks,

Michael Singmin

 

[Dave Peterson]

One manual way to check.

Show the control toolbox toolbar (view|toolbars)
click on the design mode icon
right click on the listbox and select properties
You can see the name there (and change it, too)

Or you could add something to your code:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
Link:=False, _
DisplayAsIcon:=False, Left:=285, Top:=141, Width:=77.25, _
Height:=118.5)

MsgBox OLEObj.Name

'you can even name it in code
With OLEObj
.Name = "lb_" & .TopLeftCell.Address(0, 0)
End With

End Sub

 

[Dave Peterson]

And when I'm stepping through this kind of code, I'll set a break point right
after that offending line. I'll step as far as I want then hit F5 (run) to get
past that problem line.