Images off of a worksheet

J

joederr

Is there a way yet on how to pull (or put for that matter) an image tha
is on a worksheet to a userform in excel VB?

I have an image that maybe changed from time to time, but when you pul
up the file, the image should be there, I have the image on th
worksheet in A1 then B1 I have First Name C1 Last Name etc.. I ca
populate everything but the picture.

Help!

Thanks in advance!

Joe Der
 
T

Tom Ogilvy

it would be fairly complex. You can look at Stephen Bullen's site

http://www.bmsltd.ie

go to the Excel page and find the pastepicture.zip file which contains a
workbook with sample code.
 
R

Ron de Bruin

Doug posted this in Januari

***********************************************************
I'm assuming you need to load the picture at runtime. I struggled with the
same thing a few months ago and could not get the syntax. I tried something
like Image1.Picture = thisworkbook.worksheets("birds").shapes("birds1"), but
it didn't work, and nobody in this group responded with any syntax.
Somebody in this group suggested storing the images in the userform and
that's what I did. I put the images on the right side of the userform and
then set the right margin so that they were beyond it. Increased the size
of the file, but it worked.



--
Regards Ron de Bruin
http://www.rondebruin.nl


Tom Ogilvy said:
it would be fairly complex. You can look at Stephen Bullen's site

http://www.bmsltd.ie

go to the Excel page and find the pastepicture.zip file which contains a
workbook with sample code.
Click to expand...
 
M

Michel Pierron

Hi joederr;
Can be as follows:

Option Explicit
Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(8) As Byte
End Type

Private Type PICTDESC
cbSize As Long
picType As Long
hImage As Long
End Type

Private Declare Function OpenClipboard& Lib "user32" (ByVal hwnd As Long)
Private Declare Function GetClipboardData& Lib "user32" (ByVal wFormat%)
Private Declare Function CloseClipboard& Lib "user32" ()
Private Declare Function CopyImage& Lib "user32" (ByVal handle& _
, ByVal un1&, ByVal n1&, ByVal n2&, ByVal un2&)
Private Declare Function IIDFromString Lib "ole32" (ByVal lpsz As String _
, ByRef lpiid As GUID) As Long
Private Declare Function OleCreatePictureIndirect Lib "olepro32" _
(pPictDesc As PICTDESC, ByRef riid As GUID, ByVal fOwn As Long _
, ByRef ppvObj As IPicture) As Long

' picTypeConstants:
' None = 0 / Bitmap = 1 / Metafile = 2 / Icon = 3 / EMetafile = 4
Sub CopyImgToForm()
ThisWorkbook.Sheets(1).Shapes(1).CopyPicture xlScreen, xlBitmap
Dim hCopy&
OpenClipboard 0&
hCopy = CopyImage(GetClipboardData(2), 0, 0, 0, &H4)
CloseClipboard
If hCopy = 0 Then Exit Sub
Const IPictureIID = "{7BF80981-BF32-101A-8BBB-00AA00300CAB}"
Dim iPic As IPicture, tIID As GUID, tPICTDEST As PICTDESC, Ret&
Ret = IIDFromString(StrConv(IPictureIID, vbUnicode), tIID)
If Ret Then Exit Sub
With tPICTDEST
.cbSize = Len(tPICTDEST)
.picType = 1
.hImage = hCopy
End With
Ret = OleCreatePictureIndirect(tPICTDEST, tIID, 1, iPic)
If Ret Then Exit Sub
UserForm1.Picture = iPic
'UserForm1.Image1.Picture = IPic
Set iPic = Nothing
UserForm1.Show
End Sub

MP
 
Top