VBA: How to reference a ListBox not in a UserForm?

[Eyes0ftheworld]

Hi All!

I'm having issues referencing a ListBox within a spreadsheet. I'm using Mac Excel 2016 and they unfortunately went away with UserForms all together, so I've been forced to build my own forms within the cells.

Currently, when I want to add a value to ListBox based on a cell value, I use the below code:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00; background-color: #ffffff}</style>[COLOR=#008F00][FONT=Menlo]Worksheets("Directions").Shapes("SELECTEDGEOListBox").OLEFormat.Object.AddItem Worksheets("Directions").Range("A103").Value[/FONT][/COLOR]

I feel like there's a simpler way to reference a listbox or button within a spreadsheet. I'm always running into errors when I try and build out macros. When they used to be in a UserForm, it was as simple as just referencing the shapes' name. Any help here would be great. Thanks!

 

[RoryA]

Try:

Worksheets("Directions").Listboxes("SELECTEDGEOListBox").AddItem Worksheets("Directions").Range("A103").Value

 

[BQardi]

There is. You don't need VBA for what you try to accomplish with your code...

Create a named range f.ex in Sheet2 cell A1, let's call this named range "myID"
Create a Named range with the values you want listed in your ListBox, f.ex Sheet2 A2:A5, call it "myList"
(You don't need named ranges, but I find it easier to work with)

Rightclick your ListBox and select "Format control..."
In "Input range:" insert the name "myList"
In "Cell link:" insert the name "myID"
Then in Sheet1 cell A103, insert the formula:

=INDEX(myList,myID)

You can make "myList" dynamic by going to the name manager and insert this formula for "myList":

=OFFSET(Sheet2!$A:$A;1;;COUNTA(Sheet2!$A:$A)-1)

EDIT: You where showing a different code before where you used:

With Worksheets("Sheet1").Shapes("ListBox").ControlFormat
    Worksheets("Sheet1").Range("A103") = .List(.Value)
End With

Which my answer was based on...