VBA/Excel: Run-Time Error 1004

NaughtyPopz

New Member
Joined
Jun 27, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0
This works for me. Change references where required.
Code:
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.
 
Upvote 1
Solution
Get rid of Merged cells. Read these articles.

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.
 
Upvote 1
This works for me. Change references where required.
Code:
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!!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top