VBA ListBox add item using worksheet variable

Question

I have an ActiveX ListBox called "Listbox1" in "Start" worksheet. Why ThisWorkbook.Worksheets("START").ListBox1.AddItem ("a") works and shStart.ListBox1.AddItem ("b") didn't.

I received this error: Method or data member not found

Dim tw As Workbook
Dim shStart As Worksheet
Set tw = ThisWorkbook
Set shStart = tw.Worksheets("START")

ThisWorkbook.Worksheets("START").ListBox1.AddItem ("a")
shStart.ListBox1.AddItem ("b")

GSerg · Accepted Answer · 2019-01-15 10:39:48Z

2

The Worksheet class is non-extensible. This means that if you declare a variable of the type Worksheet, you can only call methods and properties that are pre-declared with the Worksheet type. You cannot access any methods or properties that you defined on the sheet in addition to that.

Your options are:

Declare the variable as the type of the exact sheet (using its codename)
```
Dim shStart as Sheet1
```
Declare the variable as Object
Keep using the Worksheets("START") syntax (it returns an Object too, which is why it also works).

edited Jan 15, 2019 at 10:39

answered Jan 15, 2019 at 10:35

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA ListBox add item using worksheet variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related