Controls not located when Worksheet is defined (VBA)

[kelly mort]

I have an activeX image control on a Worksheet.

It works fine when do things like :

Sheet1.Image1.Picture = LoadPicture("")

OR

With Sheet1 
     .Image1.Picture = LoadPicture("")
End With

But when I do:

Dim Sh As Worksheet 
Set Sh = Sheet1
Sh.Image1.Picture = LoadPicture("")

It says the control can't be found.

Can someone tell me why the last code is not working?

Thanks in advance.
Kelly

 

[bobsan42]

Try:

Sh.shapes("Image1").Picture = LoadPicture("")

And i would recommend also to use fully qualified references, e.g.

Set Sh = Thisworbook.Worksheets("Sheet1")

 

[RoryA]

The Worksheet class doesn’t have an Image1 property. Declare the variable as Object or as Sheet1

 

[kelly mort]

Try:

Sh.shapes("Image1").Picture = LoadPicture("")

And i would recommend also to use fully qualified references, e.g.

Set Sh = Thisworbook.Worksheets("Sheet1")

Now the error didn't show up but the code that loads image into the control didn't load the image.

 

[kelly mort]

The Worksheet class doesn’t have an Image1 property. Declare the variable as Object or as Sheet1

I am not getting what you mean clearly - can you give an example please?

 

[RoryA]

Use either:

Dim Sh As Object

or:

Dim Sh As Sheet1

 

[kelly mort]

Use either:

Dim Sh As Object

or:

Dim Sh As Sheet1

Wow. That's amazing.

I just learned 2 new tricks.

Have a wonderful time

 

[RoryA]

If you want to use a Worksheet variable, you can refer to the control using the OLEObjects collection:

sh.oleobjects("image1").object