Excel VBA photo as background TextBox

[Styx]

Hi All,
Simple question, but I can't solve it.
I have a working code who inserts a textbox and fills it with a snapshot taken with Snipper-tool (if present in a range) as background.
But I want an imported photo (in that same range) to be the background of that textbox instead of a Snipper-snapshot.
What is the difference between those 2?
Thanks in advance!

 

[sijpie]

can you post the code that you have please (put the code between code tags: click on the small VBA icon, then paste the code)

 

[Styx]

Hi Sijpie,
I figured it out myself, just searched in the forum for pieces of code I could use.
Now I do not use a textBox or Chart, but I am able to insert the picture with the correct size in a range of merged cells.
See code below, It may be not the most efficient code but it works perfectly for me.
I left out some product specific code, but left in: getting the file location, load the picture and depending if Xsize or Ysize of picture is larger it will fill the merged cell with a scaled picture while keeping the original proportions.
Any tips for better code are welcome because I am not the best programmer.

 Sub Insert_2_Images()

Dim rng As Range, wsh As Worksheet
Dim sFileName As String
Dim sFileName2 As String
Dim wia As Object
Dim PicWd As Double
Dim PicHt As Double
Dim xpicturesize As Double
Dim ypicturesize As Double
Dim Image As String
Dim Image2 As String
Dim Proportion As Double
Dim Proportion2 As Double
Dim ScaleX As Double
Dim ScaleY As Double
    
Range("B20:I20").Select 'Both pictures to be inserted will have the same size
For Each cell In Selection.Cells.Columns(1)
    ypicturesize = ypicturesize + cell.Height
  Next cell
For Each cell In Selection.Cells.Rows(1)
    xpicturesize = xpicturesize + cell.Width
  Next cell

Image = Range("AB3").Value 'name and location picture1
Image2 = Range("AB4").Value 'name and location picture2

Set wia = CreateObject("WIA.ImageFile") 

wia.LoadFile Image
PicWd = wia.Width
PicHt = wia.Height

Proportion = PicWd / PicHt
Proportion2 = xpicturesize / ypicturesize

ScaleX = PicWd / xpicturesize
ScaleY = PicHt / ypicturesize

If Proportion < Proportion2 Then
                    PicWd = PicWd / ScaleY
                    PicHt = PicHt / ScaleY
    Else
                    PicWd = PicWd / ScaleX
                    PicHt = PicHt / ScaleX
End If

Set wia = Nothing

    Set rng = Range("B20:I20") 'merged cells where picture1 should fit in
    rng.Select
    Set wsh = rng.Parent
    sFileName = Image

'Insert picture 1
wsh.Shapes.AddPicture sFileName, msoFalse, msoTrue, _
        rng.Left, rng.Top, PicWd, PicHt
Range("AB3").Value = ""
        
Set rng2 = Range("L20:R20") 'merged cells where picture2 should fit in
    rng2.Select
    Set wsh2 = rng2.Parent
    sFileName2 = Image2

'Insert picture 2
wsh2.Shapes.AddPicture sFileName2, msoFalse, msoTrue, _
        rng2.Left, rng2.Top, PicWd, PicHt
Range("AB4").Value = ""

End Sub