Debugging Adding a ListBox to a Worksheet - cannot use Break Mode

[CWMacNut]

I was going to develop a UserForm but decided that just adding a ListBox to the Worksheet would be simpler. I have a decent reference book to help me but I'm finding it is a bit outdated. I've placed my code below. The first line does not allow me to go into Break Mode. So I can only debug by running the whole thing. I'm getting an error but because I'm not in Break Mode, it does not highlight the offending line.

Error Message: Run-time error '438': Object doesn't support this property or method

Dim PrinterList$

Sub lbPrintersPopulate()
        ActiveSheet.OLEObjects.Add ClassType:="Forms.ListBox.1", Left:=47.25, Top:=43.5, Width:=68.25, Height:=15
        ActiveSheet.ListBox1.Name = "PrinterList"
        ActiveSheet.PrinterList.ColumnCount = 4
        ActiveSheet.PrinterList.Clear
        ActiveSheet.PrinterList.Object.ListFillRange = Worksheets("ListOfPrinters").Name.Range("A1:A4")
End Sub

If anyone has a clue as to which line might be the offending one, please help!!

 

[ncrcnbl]

I've been using VBA for a little while now but I don't usually add controls to a sheet, and I don't usually add them programmatically
This behavior is odd to me, I see something written about it here
https://www.pcreview.co.uk/threads/...ntrols-via-vba-kills-global-vba-heap.3763287/
https://www.ozgrid.com/forum/forum/help-forums/excel-general/54299-dynamic-creation-of-list-box

It looks like you're trying to programmatically add a listbox to the page, not just draw one on there with the developer tools, or create a form and then add a listbox to it -- I'm sorry I'm not more help here and I'm also, like you, confused that this looks more complicated than I would have expected.

It also looks like what the macro recorder shows when you add an activex control -- try the Excel Form Controls, instead of adding the ActiveX control --
ActiveSheet.ListBoxes.Add(206.25, 95.25, 417, 129.75).Select

 

[Domenic]

Try...

        Dim newOleObject As oleObject
        Set newOleObject = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Left:=47.25, Top:=43.5, Width:=68.25, Height:=15)
        newOleObject.Name = "PrinterList"

Hope this helps!

 

[CWMacNut]

Thank you ncrcnbl! I will definitely look into those links.

 

[CWMacNut]

Thank you Domenic! I will definitely try that and let you know if it works.

 

[ncrcnbl]

Programmatically adding a listbox to a userform is a bit of an advanced task, too. It's usually better to design the userform with the listbox on it, programmatically adding content to the listbox is probably where you will be where your work will go.