Excel VBA copy pasted picture transparency

Question

I need to copy a specific range and paste the range as a picture (without default excel borders).

ActiveSheet.Range(Cells(1, 1), Cells(10, 20)).Select

Selection.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
    ActiveSheet.Paste

With Selection
    .Name = "My Pic"                                                
    .ShapeRange.Fill.Transparency = 0.5             '50% transparency
End with

I have also tried this code line, but without any success.

ActiveSheet.Shapes("My Pic").Fill.Transparency = 0.5

I can change the transparency of shapes with my current code but unable to change the transparency of a picture through VBA. Although I can change the transparency of a picture manually, i cant do it with VBA Macro. I have also tried to record a macro, but no code line is recorded when I change the transparency manually (weird?!). Any leads would be much appreciated! :)

Ivan Ivan · Accepted Answer · 2021-03-16T13:03:35

Apparently to change picture transparency via VBA, you need to embed it in a shape, for example, a rectangle. This works:

Sub Macro1()
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, 63, 239, 269.5, 57).Select
    With Selection.ShapeRange.Fill
     .UserPicture PictureFile:="C:\Users\user1\Pictures\my_pic.jpg"
     .Visible = True
     .Transparency = 0.5
    End With
End Sub

Excel VBA copy pasted picture transparency

1 Answers