How to insert and resize a picture based on a cell reference

[Geerod]

I have been trying to get a picture to upload from a directory (listed in cell E8) and to resize itself to fit into a merged cell I made (cell J10... which takes up J10:R20). I want this to check the directory and if it has changed, re-upload the new image in the directory listed in that cell.

I came up with this macro which worked for a while...




Sub Pic1()

Dim Loc As String

Loc = Range("E8").Text

ActiveSheet.Pictures.Insert (Loc)

With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
.LockAspectRatio = False
.Top = Range("J10:R20").Top
.Left = Range("J10:R20").Left
.Height = Range("J10:R20").Height
.Width = Range("J10:R20").Width
End With
End Sub


Sub Reload()

Dim pic As Picture

For Each pic In ActiveSheet.Pictures
If pic.Name <> "Picture 1" Then
pic.Delete
End If
Next pic

Pic1
Pic2
Pic3
Pic4

End Sub



But when I changed the directory to a new directory, it stopped working with 400 error flashing up every time I run it.

Any ideas?

Thanks in Advance.

 

[GlennUK]

What do you mean:

I want this to check the directory and if it has changed, re-upload the new image in the directory listed in that cell.

... if what has changed???

 

[Geerod]

If the directory has changed. For instance, if I have changed it from C:\Program Files to C:\Stuff in the cell.

 

[GlennUK]

What is the full content of the cell? You seem to be saying that it's just directory text ... but you must mean that the filename is in there too ... right?

 

[Geerod]

The full content of the cell is: C:\Directory\Picture\10002.jpg

And sorry, this is my code in PHP format:

Sub Pic1()

Dim Loc As String

Loc = Range("E8").Text

ActiveSheet.Pictures.Insert (Loc)

With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
.LockAspectRatio = False
.Top = Range("J10:R20").Top
.Left = Range("J10:R20").Left
.Height = Range("J10:R20").Height
.Width = Range("J10:R20").Width
End With
End Sub

Sub Reload()

Dim pic As Picture

For Each pic In ActiveSheet.Pictures
If pic.Name <> "Picture 1" Then
pic.Delete
End If
Next pic

Pic1
Pic2
Pic3
Pic4

End Sub

 

[GlennUK]

On what row of code does the 400 error occur? Did you check that C:\Directory\Picture\10002.jpg exists?

 

[Geerod]

How would I find out what row it occured on? When I run it, no line gets highlighted like it usually does when there's an error. A box just pops up that says 400.

Yes, the file definitely exists.

 

[GlennUK]

You could step through the code ... F8 executes the macro one row of code at a time ... that might give you some clues.

 

[Geerod]

Interesting... when I run it this morning it works fine (without any changes being made) when it didn't work at all yesterday afternoon.

 

[GlennUK]

Interesting... when I run it this morning it works fine (without any changes being made) when it didn't work at all yesterday afternoon.

Ah, the joys of programming. That's the kind of thing that happens sometimes