vba for inserting picture into textbox

[robertkrz]

Hi all,
I know how to insert picture in to worksheet by VBA but can not figure out how to insert a picture into a textbox (preferably with keeping ratio).
Have you seen such anywhere ? ray:

 

[Juan Pablo González]

By TextBox you mean the TextBox from the 'Drawing' toolbar ? if so, you do it something like this:

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> ShapePicture()
    <SPAN style="color:#00007F">Dim</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> Shape
    
    <SPAN style="color:#00007F">Set</SPAN> Sh = Sheets("Sheet1").Shapes(1)
    Sh.Fill.UserPicture "D:\My Documents\My Pictures\DSC00090.JPG"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

If you want to resize the shape to adjust the ratio of the picture, that requires a bit more work:

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> ShapePicture2()
    <SPAN style="color:#00007F">Dim</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> Shape, Pic <SPAN style="color:#00007F">As</SPAN> IPictureDisp
    
    <SPAN style="color:#00007F">Set</SPAN> Pic = LoadPicture("D:\My Documents\My Pictures\DSC00090.JPG")
    <SPAN style="color:#00007F">Set</SPAN> Sh = Sheets("Sheet1").Shapes(1)
    
    <SPAN style="color:#007F00">'Resize the picture, keeping the Width constant</SPAN>
    Sh.Height = Pic.Height / Pic.Width * Sh.Width
    
    <SPAN style="color:#007F00">'Clear Pic from memory</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Pic = LoadPicture("")
    <SPAN style="color:#00007F">Set</SPAN> Pic = <SPAN style="color:#00007F">Nothing</SPAN>
    
    <SPAN style="color:#007F00">'Load the picture</SPAN>
    Sh.Fill.UserPicture "D:\My Documents\My Pictures\DSC00090.JPG"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

 

[robertkrz]

Hi Juan,
Thanks a lot. Works perfectly.
BR,ROBert