Sheet7 vs. decleared Sheet7 .image not working

[MarkCBB]

Hi there,

I think i am losing my mind a bit, I get the following error "Complie error: Method or data member not found"

I am using the following code:

Sub AddPicture()
Dim MyPictureName1 As String
Dim MyPictureName2 As String
Dim MyPictureName3 As String
Dim SHT_LIST As Worksheet
Dim SHT_FilteredOutlet_DB As Worksheet
Dim SHT_Report5 As Worksheet
Dim i As Long
Set SHT_FilteredOutlet_DB = Sheet12
Set SHT_LIST = Sheet2
Set SHT_Report5 = Sheet7
i = SHT_LIST.Range("C11").Value
MyPictureName1 = SHT_FilteredOutlet_DB.Range("E4").Offset(i, 0).Value & " Front of store"
MyPictureName2 = SHT_FilteredOutlet_DB.Range("E4").Offset(i, 0).Value & " Inside of Store"
MyPictureName3 = SHT_FilteredOutlet_DB.Range("E4").Offset(i, 0).Value & " Nappies"
If Len(Dir("D:\testing\" & MyPictureName1 & ".png", vbDirectory)) < 0 Then MyPictureName1 = "HUGGIES"
If Len(Dir("D:\testing\" & MyPictureName1 & ".png", vbDirectory)) < 0 Then MyPictureName2 = "HUGGIES"
If Len(Dir("D:\testing\" & MyPictureName1 & ".png", vbDirectory)) < 0 Then MyPictureName3 = "HUGGIES"
SHT_Report5.Image1.Picture = LoadPicture("D:\testing\" & MyPictureName1 & ".png") ' DOES NOT WORK
Sheet7.Image1.Picture = LoadPicture("D:\testing\" & MyPictureName1 & ".png") ' WORKS FINE
End Sub

But this line give me the above error:

SHT_Report5.Image1.Picture = LoadPicture("D:\testing\" & MyPictureName1 & ".png") ' DOES NOT WORK

and this one works fine:

heet7.Image1.Picture = LoadPicture("D:\testing\" & MyPictureName1 & ".png") ' WORKS FINE

Why would it not pick up the control if the Sheet is decleared?

 

[Andrew Poulsom]

If you are using the CodeName why would you want to assign it to a Worksheet object? The syntax for a Worksheet is different from the syntax for the Class:

SHT_Report5.OLEObjects("Image1").Object.Picture = LoadPicture("D:\testing\" & MyPictureName1 & ".png")

 

[MarkCBB]

o ok I see, I have alway just assigned Names to the sheets so when reading the code it is easier for me to know what is on the sheet and what sheet it is.
Thanks again for the help.