Run-time error when retrieving value from FormControl ListBox

Question

I added a FormControl ListBox with the name ListBox2 to an Excel worksheet and I assigned a Macro RectangleRoundedCorners3_Click to a shape. Now I would like to print the first item of the list box into cell A1:

Sub RectangleRoundedCorners3_Click()

ActiveSheet.Range("A1").Value = ActiveSheet.ListBox1.List(0)

End Sub

I get the following error:

Run-time error '438':

Object doesn't support this property or method

I don't really understand why this happens. If I use an ActiveX Controls ListBox the exact same code works perfectly fine.

score 3 · Accepted Answer · edited Oct 23 '19 at 09:29

3

Try:

ActiveSheet.Range("A1").Value = Activesheet.ListBoxes("ListBox2").List(1)

Note: a reference to the ActiveSheet is not the best way to reference a sheet. Have a look at this post here to read more about it.

A helpful overview of FormControl ListBoxes using several methods can be found at the site Complete guide to Excel VBA Form Control ListBoxes. - Edited due to Jonas' comment :-)

edited Oct 23 '19 at 09:29

T.M.

9,436
3
33
57

answered Oct 23 '19 at 07:20

JvdV

70,606
8
39
70

1

Works! Thank you a lot! I will check out the link too. Thanks – Stücke Oct 23 '19 at 07:25
1

Helpful link [](https://wellsr.com/vba/2016/excel/complete-guide-to-excel-vba-form-control-listboxes/) – T.M. Oct 23 '19 at 07:59
@T.M. a very helpfull link. feel free to edit into the answer =) – JvdV Oct 23 '19 at 08:01

Run-time error when retrieving value from FormControl ListBox

1 Answers1