VBA/Excel: Run-Time Error 1004

[NaughtyPopz]

Hello everyone,

it's the first time that I'm trying to work with excel/vba.
I keep getting a Run-Time error 1004: Unable to get the Insert property of the picture class. (when i remove the 'on error').
When I debug, it marks the ActiveSheet.Pictures.Insert(picname).Select.

Can someone please tell me what I'm missing or doing wrong?

Thanks.



Sub Picmacro()

Range("A1").Select
Dim picname As String
Dim targetCell As Range
Dim ws As Worksheet

Set ws = ActiveSheet
Set targetCell = ws.Range("A1")

picname = Range("D3") & ".jpg" 'Link to the picture

ActiveSheet.Pictures.Delete

On Error GoTo ErrNoPhoto

ActiveSheet.Pictures.Insert(picname).Select

With Selection
.Height = targetCell.MergeArea.Height
.Top = targetCell.Top
.Left = targetCell.Left + (targetCell.MergeArea.Width - .Width) / 2
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 121#
.ShapeRange.Width = 120#
.ShapeRange.Rotation = 0#
End With

Range("A10").Select
Application.ScreenUpdating = True

Exit Sub

ErrNoPhoto:
MsgBox "No picture found" & Chr(13) & "Please upload an image"
Range("A1").Value = "Picture not found"

Exit Sub
Range("A20").Select

End Sub

 

[pjmorris]

A few thoughts:

1. Replace Activesheet with Worksheet("Sheetname") so that it is specifically referenced.
2. Are you sure that the value in D3 provides the full path to the image?
3. Does the image actually exist?

Hope that helps.

 

[NaughtyPopz]

A few thoughts:

1. Replace Activesheet with Worksheet("Sheetname") so that it is specifically referenced.
2. Are you sure that the value in D3 provides the full path to the image?
3. Does the image actually exist?

Hope that helps.

Thanks for your reply.

The image does exist and is located in the same map as the excel file.

I will try the worksheet later today and keep you uptodate.

 

[NaughtyPopz]

A few thoughts:

1. Replace Activesheet with Worksheet("Sheetname") so that it is specifically referenced.
2. Are you sure that the value in D3 provides the full path to the image?
3. Does the image actually exist?

Hope that helps.

Now it gives me runtime error 9: subscript out of range.

 

[MARK858]

It should be Worksheets("YourSheetName"), if you still get the same error then check the spelling, spaces etc. in your sheet name.

As for your original question, do you have any sheet protection?

 

[NaughtyPopz]

It should be Worksheets("YourSheetName"), if you still get the same error then check the spelling, spaces etc. in your sheet name.

As for your original question, do you have any sheet protection?

Still the same error, it's kinda strange. It worked for like 5 seconds tho..

 

[MARK858]

Subscript out of range is normally a naming/spelling error as the code cannot find the stated object and the only naming part that I can see in that line is the sheet name.
Please post all of your code as you currently have it.

P.S. you also haven't stated if you have any sheet protection yet

 

[jolivanes]

This works for me. Change references where required.

Sub Add_Pic()
Dim sh1 As Worksheet, picName As String
Set sh1 = Worksheets("Sheet1")    '<---- Change as required
picName = ThisWorkbook.Path & "\" & sh1.Range("D3").Value & ".jpg"
ActiveSheet.Shapes.AddPicture(picName, False, True, sh1.Cells(1, 1).Left, sh1.Cells(1, 1).Top, _
sh1.Cells(1, 4).Left, sh1.Cells(11, 1).Top).Name = "Ma_Look_Here"
End Sub

Add the rest of your code as desired after.

 

[jolivanes]

Get rid of Merged cells. Read these articles.

Excel Tips: Why You Should Avoid Merging Cells

Use the Center Across Selection feature to easily merge cells in Excel.

edu.gcfglobal.org

Stop! Do Not Merge Cells in Excel - Heres why with fixes - The Excel Club

Find out why you should not merge cells in excel, the problems merged cells can cause and how you can overcome this problem.

theexcelclub.com

Do not merge cells in Excel! // PerfectXL Risk Eliminator

It's so tempting to merge cells in Excel so that they form a header above two or more columns. But resist the temptation; it can be dangerous!

www.perfectxl.com

Note:
"Pictures.Insert" has a reference to that picture (unless specifically deleted)
Picture will not stay with workbook if picture is not in the mentioned folder.
"Shapes.AddPicture" inbeds picture and stays with workbook.

 

[NaughtyPopz]

This works for me. Change references where required.

Sub Add_Pic()
Dim sh1 As Worksheet, picName As String
Set sh1 = Worksheets("Sheet1")    '<---- Change as required
picName = ThisWorkbook.Path & "\" & sh1.Range("D3").Value & ".jpg"
ActiveSheet.Shapes.AddPicture(picName, False, True, sh1.Cells(1, 1).Left, sh1.Cells(1, 1).Top, _
sh1.Cells(1, 4).Left, sh1.Cells(11, 1).Top).Name = "Ma_Look_Here"
End Sub

Add the rest of your code as desired after.

This works! Thanks alot!!