excel - How to make picture %60 transparency in vba? - Stack Overflow

admin管理员组

文章数量:1310508

The following video shows how to make picture transparency.

I am looking for vba codes which make picture %60 transparency.

Macro recording doesnt work.

First answer of the following link doesnt help because I dont want to add extra rectangle (shape) in order to keep my codes simple.

Second answer of the following link doesnt help because it makes only %100 transparent. I want %60 transparency. It applies to bitmaps only.

Set image transparency with VBA in Excel

The following video shows how to make picture transparency.

https://www.youtube/watch?v=9RpIELN3lyI

I am looking for vba codes which make picture %60 transparency.

Macro recording doesnt work.

First answer of the following link doesnt help because I dont want to add extra rectangle (shape) in order to keep my codes simple.

Second answer of the following link doesnt help because it makes only %100 transparent. I want %60 transparency. It applies to bitmaps only.

Set image transparency with VBA in Excel

• excel
• vba

Share Improve this question Follow edited Feb 3 at 21:45 Kram Kramer asked Feb 3 at 12:09 Kram KramerKram Kramer 12111 silver badge66 bronze badges 2

• 1 "doesn't help" is not helpful. What happens when you try the solutions on that link? – CHill60 Commented Feb 3 at 12:15
• ^^^ and when you tried it, did you change the transparency colour from RGB(255, 255, 255) to RGB(153, 153, 153) (153 is 60% of 255)? – cybernetic.nomad Commented Feb 3 at 14:35

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

I have to admit it was a lot more challenging than I thought it would be (or course I might have overcomplicated it):

Sub InsertRectangleWithImage()
    Dim ws As Worksheet
    Dim shp As Shape
    Dim imgURL As String
    Dim imgPath As String
    
    Set ws = ThisWorkbook.Sheets("Sheet1")

    imgURL = "https://cdn.hubblecontent.osi.office/m365content/publish/0b4f7be0-2a71-43c6-a762-bba962e256c3/182654618.jpg"

    imgPath = Environ("TEMP") & "\tempImage.jpg"
    DownloadFile imgURL, imgPath

    Set shp = ws.Shapes.AddShape(msoShapeRectangle, 50, 50, 200, 100)

    shp.Fill.UserPicture imgPath
    
    shp.Fill.Transparency = 0.4

    Kill imgPath
End Sub


Private Sub DownloadFile(URL As String, LocalFilePath As String)
    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    WinHttpReq.Open "GET", URL, False
    WinHttpReq.Send

    If WinHttpReq.Status = 200 Then
        Dim adoStream As Object
        Set adoStream = CreateObject("ADODB.Stream")
        adoStream.Open
        adoStream.Type = 1 ' Binary
        adoStream.Write WinHttpReq.ResponseBody
        adoStream.Position = 0 ' Set the stream position to the start
        adoStream.SaveToFile LocalFilePath, 2 ' Overwrite if file exists
        adoStream.Close
    End If
End Sub

Reference the shape and it's Fill.Transparency property.

Public Sub Test()

    ChangeTransparency "Picture 3", 0.6
    ChangeTransparency "Rectangle 4", 0.3
    ChangeTransparency "Graphic 6", 0.8

End Sub

Public Sub ChangeTransparency(ShapeName As String, TransparencyPercent As Double)

    Dim MyShape As Shape
    
    Set MyShape = ThisWorkbook.Worksheets("Sheet1").Shapes(ShapeName)
    MyShape.Fill.Transparency = TransparencyPercent

End Sub  

As a single line:

ThisWorkbook.Worksheets("Sheet1").Shapes("Rectangle 4").Fill.Transparency = 0.3

Edit:
Having looked at your second link I think you're after making a picture transparent rather than the background of the picture which you've probably found the code doesn't do if you use insert picture.
Instead insert a rectangle and set its fill to a Picture or texture fill.

本文标签： excelHow to make picture 60 transparency in vbaStack Overflow