VBA to insert a custom image

[dpaton05]

I have code to insert a set image. Could someone tell me how to alter it so it can insert a custom image please?

Sub cmdJakeSig()
    Dim shp As Shape
    Set shp = ThisWorkbook.Worksheets("sheet1").Shapes("textbox4")
        Sheets("Sheet2").Shapes("ImgJ").Copy
        Sheets("sheet1").Paste Destination:=Sheets("sheet1").Cells(1, 1)
        Selection.Top = shp.Top + shp.Height + "50"
    'ActiveSheet.Protect Password:=""
End Sub

 

[dpaton05]

BTW, you need to actually insert a Page Break at row 37, for the code to work !!!

What do you mean by that Michael?

 

[Michael M]

Just a thought, but have you considered adding the image to the Page footer instead of trying to fit it into the actual page ??

Sub MM1()
With ActiveSheet.PageSetup
    fNameAndPath = Application.GetOpenFilename(Title:="Select Signature To Be Imported")    
    .LeftFooterPicture.fileName = fNameAndPath
    .LeftFooter = "&G"
End With
End Sub

 

[dpaton05]

How do I do that?

 

[dpaton05]

I was just thinking it would be possible to measure the height of the image and if it won't fit in the section of several lines below the notes box to the bottom of the page, to have it inserted in the next page.

 

[dpaton05]

Is there a setting in excel to stop an image being split over 2 pages?

 

[dpaton05]

Just a thought, but have you considered adding the image to the Page footer instead of trying to fit it into the actual page ??

Sub MM1()
With ActiveSheet.PageSetup
    fNameAndPath = Application.GetOpenFilename(Title:="Select Signature To Be Imported")    
    .LeftFooterPicture.fileName = fNameAndPath
    .LeftFooter = "&G"
End With
End Sub

But if it gets pushed to the additional page, it will be at the bottom and I need it at the top.

 

[dpaton05]

Also, isn't the page footer on every page, I need it just once at the end of the document.

 

[dpaton05]

What if you insert a page break if below textbox 4 if there are only 6 or less rows to the end of the current page and then insert the picture at the top of the next page?

 

[Michael M]

Is there a setting in excel to stop an image being split over 2 pages?

Not that I'm aware of...otherwise we would have used it !!

 

[Michael M]

How do I do that?

Select A37>>Page Layout>>Page Setup>>Breaks>>Insert Break