1

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")
BOB
  • 700
  • 2
  • 16
  • 35

1 Answers1

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).
GSerg
  • 76,472
  • 17
  • 159
  • 346