Run-time error when retrieving value from FormControl ListBox

user_5696601 0
Viewed 92

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.

form-control vba excel listbox
1 Answers

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

user_97581940
Related