Embedded ActiveX controls

[Jack Clift]

I am trying to manipulate some embedded activeX controls
(comboBox) on a worksheet. Refer some sample coded below:

Dim w As Worksheet
Set w = ActiveWorkbook.Worksheets("help")
ActiveWorkbook.Worksheets("help").cboX.AddItem = "test"
w.cboX.AddItem = "again"

Line 3 works fine, and adds the text "help" to the combo
box.

Line 4 which I would have thought was identical, fails
even to compile!

Due to the nature of the task at hand it will be much
neater if I can get the code given (or like) line 4 to
work. Any ideas?

Thanks

Jack

 

[Chip Pearson]

Jack,

Try

w.OLEObjects("cboX").Object.AddItem "test2"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Thanks Chip,

have tried and works fine. BTW, what is the effective
difference between the two lines of code quoted below
(i.e. why does one work and not the other?)

Curious,

Jack

 

[Chip Pearson]

Jack,

It is a matter of early versus late binding. In the line of code

ActiveWorkbook.Worksheets("help").cboX.AddItem "Test"

Worksheets("Help") returns a generic Object type variable, and so
the compiler issues code to find cboX at run time, which it does
successfully and the code works as expected. This is late
binding.

In the line of code

w.cboX.AddItem "again"

w is defined as a Worksheet type variable, and the Worksheet
object does not support a method or property named cboX, so the
compiler chokes with an error. This is early binding. You can
make this line of code work by declaring w As Object rather than
As Worksheet. This causes the compiler to late bind the cboX
property, and the code will execute successfully.

Dim w As Object
w.cboX.AddItem "again"



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com