Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window.
I have userforms which I activate to show text information.
I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting the image into the picture line of the Image Properties box. This is too complicated for a user of my spreadsheet to do so I am looking for alternative way of pasting an image so that it becomes attached to a userform and shows when the user opens the form. Can anyone help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window.
You asked about this before and I tried to ask you to clarify some things
before proposing a solution, but you didn't get back. Is this something new or related to your previous question. Regards, Peter T "Roger on Excel" wrote in message ... I have userforms which I activate to show text information. I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting the image into the picture line of the Image Properties box. This is too complicated for a user of my spreadsheet to do so I am looking for alternative way of pasting an image so that it becomes attached to a userform and shows when the user opens the form. Can anyone help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
Hi Peter,
Thanks for getting back to me again. Yes, it is the same problem. I have been out of town and noticed somebody had pasted something about the user pasting the imagefrom the clipboard into the workbook which is exactly what I dont want to do. I have had a look at a few different solutions that were posted which also didnt help. Also, I think I have defined my problem a little better so I was hoping to get some more feedback afresh. Did you manage to come up with a solutin. I would very much appreciate your feedback "Peter T" wrote: You asked about this before and I tried to ask you to clarify some things before proposing a solution, but you didn't get back. Is this something new or related to your previous question. Regards, Peter T "Roger on Excel" wrote in message ... I have userforms which I activate to show text information. I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting the image into the picture line of the Image Properties box. This is too complicated for a user of my spreadsheet to do so I am looking for alternative way of pasting an image so that it becomes attached to a userform and shows when the user opens the form. Can anyone help? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
Still need to know the answers to questions I asked last time. In particular
how is the image copied, eg from a button on your form (if so do you know for sure copy of an image has succeeded), or some user copy action (if so how do you know user has done that). IOW describe the overall process from start to the time you want to extract the image from the clipboard onto (say) an image control your form. Refer to the previous thread and the information I tried to ask for. Regards, Peter T "Roger on Excel" wrote in message ... Hi Peter, Thanks for getting back to me again. Yes, it is the same problem. I have been out of town and noticed somebody had pasted something about the user pasting the imagefrom the clipboard into the workbook which is exactly what I dont want to do. I have had a look at a few different solutions that were posted which also didnt help. Also, I think I have defined my problem a little better so I was hoping to get some more feedback afresh. Did you manage to come up with a solutin. I would very much appreciate your feedback "Peter T" wrote: You asked about this before and I tried to ask you to clarify some things before proposing a solution, but you didn't get back. Is this something new or related to your previous question. Regards, Peter T "Roger on Excel" wrote in message ... I have userforms which I activate to show text information. I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting the image into the picture line of the Image Properties box. This is too complicated for a user of my spreadsheet to do so I am looking for alternative way of pasting an image so that it becomes attached to a userform and shows when the user opens the form. Can anyone help? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window.
Dear Peter,
The steps are as follows 1) User creates chemical structure picture in ChemDraw 2) user draws a selection box around structure and presses ctrlC 3) user opens excel file 4) user activates form 5)user pastes (ctrlV) structure into some sort of dialog box/text box/other control?? on the form- such a control may not exist 6) Excel appends the copied image as a picture onto another predefined user form (or the same form) 7) when user opens the predefined userform the pasted image appears on the form Not sure if I can describe this any other way apart from the fact that my spreadsheet is effectively run using user forms with data displayed on userforms. I am trying to get away from the uder having to paste pictures directly into the sheets themselves since the spreadsheet is driven by userforms Best regards, Roger "Roger on Excel" wrote: I have userforms which I activate to show text information. I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting the image into the picture line of the Image Properties box. This is too complicated for a user of my spreadsheet to do so I am looking for alternative way of pasting an image so that it becomes attached to a userform and shows when the user opens the form. Can anyone help? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window.
Have a go with the following. Put a button and and image control on a form.
For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub '''' In a Normal Module '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' '' Based almost entirely on Stephen Bullen's "PastePicture.Zip" '' available from http://www.oaltd.co.uk '' Code below is copied from modPastePicture with headers and some '' comments removed. Function fnOLEError in the original module '' is also removed but shold be added back for completion. '' '' An additional new function - WhatsInClipboard() is included '' '' Recommend obtain the original module and include WhatsInClipboard '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Option Explicit Option Compare Text ''' User-Defined Types for API Calls 'Declare a UDT to store a GUID for the IPicture OLE Interface Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type 'Declare a UDT to store the bitmap information Private Type uPicDesc Size As Long Type As Long hPic As Long hPal As Long End Type '''Windows API Function Declarations 'Does the clipboard contain a bitmap/metafile? Private Declare Function IsClipboardFormatAvailable Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Open the clipboard to read Private Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd As Long) As Long 'Get a pointer to the bitmap/metafile Private Declare Function GetClipboardData Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Close the clipboard Private Declare Function CloseClipboard Lib "user32" () As Long 'Convert the handle into an OLE IPicture interface. Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" ( _ PicDesc As uPicDesc, RefIID As GUID, _ ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 'Create our own copy of the metafile, so it doesn't get _ 'wiped out by subsequent clipboard updates. Declare Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" ( _ ByVal hemfSrc As Long, ByVal lpszFile As String) As Long 'Create our own copy of the bitmap, so it doesn't get wiped out by subsequent 'clipboard updates. Declare Function CopyImage Lib "user32" (ByVal handle As Long, _ ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, _ ByVal un2 As Long) As Long 'The API format types we're interested in Const CF_BITMAP = 2 Const CF_PALETTE = 9 Const CF_ENHMETAFILE = 14 Const IMAGE_BITMAP = 0 Const LR_COPYRETURNORG = &H4 Function WhatsInClipboard() As Long ' This function is not included in PastePicture.zip ' Purpose is to learn which usable picture formats are in the ' clipboard, if any, before doing 'PastePicture' Dim hPicAvail As Long Dim picTypes As Long If IsClipboardFormatAvailable(CF_BITMAP) Then picTypes = 1 End If If IsClipboardFormatAvailable(CF_ENHMETAFILE) Then picTypes = picTypes Or 2 End If WhatsInClipboard = picTypes ' 1 got a bitmap ' 2 got a metafile ' 3 got both ' 0 got neither End Function Function PastePicture(Optional lXlPicType As Long = xlPicture) As IPicture 'Some pointers Dim h As Long, hPicAvail As Long, hPtr As Long, hPal As Long Dim lPicType As Long, hCopy As Long 'Convert xl piture-type constant to the API constant equivalent lPicType = IIf(lXlPicType = xlBitmap, CF_BITMAP, CF_ENHMETAFILE) 'Check if the clipboard contains the required format hPicAvail = IsClipboardFormatAvailable(lPicType) If hPicAvail < 0 Then 'Get access to the clipboard h = OpenClipboard(0&) If h 0 Then 'Get a handle to the image data hPtr = GetClipboardData(lPicType) 'Create our own copy of the image on the clipboard, in the appropriate format. If lPicType = CF_BITMAP Then hCopy = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG) Else hCopy = CopyEnhMetaFile(hPtr, vbNullString) End If 'Release the clipboard to other programs h = CloseClipboard 'If we got a handle to the image, convert it into a Picture object and return it If hPtr < 0 Then Set PastePicture = CreatePicture(hCopy, 0, lPicType) End If End If End Function Private Function CreatePicture(ByVal hPic As Long, ByVal hPal As Long, _ ByVal lPicType) As IPicture ' IPicture requires a reference to "OLE Automation" Dim r As Long, uPicInfo As uPicDesc, IID_IDispatch As GUID, IPic As IPicture 'OLE Picture types Const PICTYPE_BITMAP = 1 Const PICTYPE_ENHMETAFILE = 4 ' Create the Interface GUID (for the IPicture interface) With IID_IDispatch .Data1 = &H7BF80980 .Data2 = &HBF32 .Data3 = &H101A .Data4(0) = &H8B .Data4(1) = &HBB .Data4(2) = &H0 .Data4(3) = &HAA .Data4(4) = &H0 .Data4(5) = &H30 .Data4(6) = &HC .Data4(7) = &HAB End With ' Fill uPicInfo with necessary parts. With uPicInfo .Size = Len(uPicInfo) .Type = IIf(lPicType = CF_BITMAP, PICTYPE_BITMAP, PICTYPE_ENHMETAFILE) .hPic = hPic .hPal = IIf(lPicType = CF_BITMAP, hPal, 0) End With ' Create the Picture object. r = OleCreatePictureIndirect(uPicInfo, IID_IDispatch, True, IPic) ' If an error occured, show the description If r < 0 Then 'Debug.Print "Create Pictu " & fnOLEError(r) Debug.Print "Error, call fnOLEError(r) here" ' fnOLEError from modPastePicture not posted End If ' Return the new Picture object. Set CreatePicture = IPic End Function '' end code Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, The steps are as follows 1) User creates chemical structure picture in ChemDraw 2) user draws a selection box around structure and presses ctrlC 3) user opens excel file 4) user activates form 5)user pastes (ctrlV) structure into some sort of dialog box/text box/other control?? on the form- such a control may not exist 6) Excel appends the copied image as a picture onto another predefined user form (or the same form) 7) when user opens the predefined userform the pasted image appears on the form Not sure if I can describe this any other way apart from the fact that my spreadsheet is effectively run using user forms with data displayed on userforms. I am trying to get away from the uder having to paste pictures directly into the sheets themselves since the spreadsheet is driven by userforms Best regards, Roger "Roger on Excel" wrote: I have userforms which I activate to show text information. I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting the image into the picture line of the Image Properties box. This is too complicated for a user of my spreadsheet to do so I am looking for alternative way of pasting an image so that it becomes attached to a userform and shows when the user opens the form. Can anyone help? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
Hi Peter,
Many thanks for helping me on this problem. I tried your code out and it works very very nicely - It puts a ChemDraw structure nicely onto the form. It also puts copied images in to the image box (which will be especially useful when cutting and pasting from other documents/sources other than chemdraw) A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. In my spreadsheet I have 10 different forms which the user calls up and each one will need to have a different chemical structure associated with and showing on it. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, it doesnt paste the selection into the form. Is there a way to do this as sometimes a user may elect to copy a structure from a pdf using the marquee tool in adobe reader and paste that (for example; if they dont have Chemdraw). Roger "Peter T" wrote: Have a go with the following. Put a button and and image control on a form. For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub '''' In a Normal Module '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' '' Based almost entirely on Stephen Bullen's "PastePicture.Zip" '' available from http://www.oaltd.co.uk '' Code below is copied from modPastePicture with headers and some '' comments removed. Function fnOLEError in the original module '' is also removed but shold be added back for completion. '' '' An additional new function - WhatsInClipboard() is included '' '' Recommend obtain the original module and include WhatsInClipboard '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Option Explicit Option Compare Text ''' User-Defined Types for API Calls 'Declare a UDT to store a GUID for the IPicture OLE Interface Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type 'Declare a UDT to store the bitmap information Private Type uPicDesc Size As Long Type As Long hPic As Long hPal As Long End Type '''Windows API Function Declarations 'Does the clipboard contain a bitmap/metafile? Private Declare Function IsClipboardFormatAvailable Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Open the clipboard to read Private Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd As Long) As Long 'Get a pointer to the bitmap/metafile Private Declare Function GetClipboardData Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Close the clipboard Private Declare Function CloseClipboard Lib "user32" () As Long 'Convert the handle into an OLE IPicture interface. Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" ( _ PicDesc As uPicDesc, RefIID As GUID, _ ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 'Create our own copy of the metafile, so it doesn't get _ 'wiped out by subsequent clipboard updates. Declare Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" ( _ ByVal hemfSrc As Long, ByVal lpszFile As String) As Long 'Create our own copy of the bitmap, so it doesn't get wiped out by subsequent 'clipboard updates. Declare Function CopyImage Lib "user32" (ByVal handle As Long, _ ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, _ ByVal un2 As Long) As Long 'The API format types we're interested in Const CF_BITMAP = 2 Const CF_PALETTE = 9 Const CF_ENHMETAFILE = 14 Const IMAGE_BITMAP = 0 Const LR_COPYRETURNORG = &H4 Function WhatsInClipboard() As Long ' This function is not included in PastePicture.zip ' Purpose is to learn which usable picture formats are in the ' clipboard, if any, before doing 'PastePicture' Dim hPicAvail As Long Dim picTypes As Long If IsClipboardFormatAvailable(CF_BITMAP) Then picTypes = 1 End If If IsClipboardFormatAvailable(CF_ENHMETAFILE) Then picTypes = picTypes Or 2 End If WhatsInClipboard = picTypes ' 1 got a bitmap ' 2 got a metafile ' 3 got both ' 0 got neither End Function Function PastePicture(Optional lXlPicType As Long = xlPicture) As IPicture 'Some pointers Dim h As Long, hPicAvail As Long, hPtr As Long, hPal As Long Dim lPicType As Long, hCopy As Long 'Convert xl piture-type constant to the API constant equivalent lPicType = IIf(lXlPicType = xlBitmap, CF_BITMAP, CF_ENHMETAFILE) 'Check if the clipboard contains the required format hPicAvail = IsClipboardFormatAvailable(lPicType) If hPicAvail < 0 Then 'Get access to the clipboard h = OpenClipboard(0&) If h 0 Then 'Get a handle to the image data hPtr = GetClipboardData(lPicType) 'Create our own copy of the image on the clipboard, in the appropriate format. If lPicType = CF_BITMAP Then hCopy = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG) Else hCopy = CopyEnhMetaFile(hPtr, vbNullString) End If 'Release the clipboard to other programs h = CloseClipboard 'If we got a handle to the image, convert it into a Picture object and return it If hPtr < 0 Then Set PastePicture = CreatePicture(hCopy, 0, lPicType) End If End If End Function Private Function CreatePicture(ByVal hPic As Long, ByVal hPal As Long, _ ByVal lPicType) As IPicture ' IPicture requires a reference to "OLE Automation" Dim r As Long, uPicInfo As uPicDesc, IID_IDispatch As GUID, IPic As IPicture 'OLE Picture types Const PICTYPE_BITMAP = 1 Const PICTYPE_ENHMETAFILE = 4 ' Create the Interface GUID (for the IPicture interface) With IID_IDispatch .Data1 = &H7BF80980 .Data2 = &HBF32 .Data3 = &H101A .Data4(0) = &H8B .Data4(1) = &HBB .Data4(2) = &H0 .Data4(3) = &HAA .Data4(4) = &H0 .Data4(5) = &H30 .Data4(6) = &HC .Data4(7) = &HAB End With ' Fill uPicInfo with necessary parts. With uPicInfo .Size = Len(uPicInfo) .Type = IIf(lPicType = CF_BITMAP, PICTYPE_BITMAP, PICTYPE_ENHMETAFILE) .hPic = hPic .hPal = IIf(lPicType = CF_BITMAP, hPal, 0) End With ' Create the Picture object. r = OleCreatePictureIndirect(uPicInfo, IID_IDispatch, True, IPic) ' If an error occured, show the description If r < 0 Then 'Debug.Print "Create Pictu " & fnOLEError(r) Debug.Print "Error, call fnOLEError(r) here" ' fnOLEError from modPastePicture not posted End If ' Return the new Picture object. Set CreatePicture = IPic End Function '' end code Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, The steps are as follows 1) User creates chemical structure picture in ChemDraw 2) user draws a selection box around structure and presses ctrlC 3) user opens excel file 4) user activates form 5)user pastes (ctrlV) structure into some sort of dialog box/text box/other control?? on the form- such a control may not exist 6) Excel appends the copied image as a picture onto another predefined user form (or the same form) 7) when user opens the predefined userform the pasted image appears on the form Not sure if I can describe this any other way apart from the fact that my spreadsheet is effectively run using user forms with data displayed on userforms. I am trying to get away from the uder having to paste pictures directly into the sheets themselves since the spreadsheet is driven by userforms Best regards, Roger "Roger on Excel" wrote: I have userforms which I activate to show text information. I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
Glad it seems to be working, after all that!
A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. If the requirement is only for the session, in the form - Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = True Me.Hide End If One way to permanently store the image would be to an ActiveX Image control (hidden) in a sheet, eg ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture reverse to load back to the form's image control Another way would be to save to file and load from file. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, Oops, my fault, not sure how such an elementary error crept in! In the userform code (as posted last time) replace CommandButton1_Click with the following Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture ElseIf lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub Trust you've got the image control's picture properties adjusted to needs, either at design or amended at runtime.. FWIW, if you want to know the overall dimensions of the image before showing it (first make invisible), allow the image to Autosize, get the new dim's, resize to original or other as required, or leave as autosize'd Regards, Peter T "Roger on Excel" wrote in message ... Hi Peter, Many thanks for helping me on this problem. I tried your code out and it works very very nicely - It puts a ChemDraw structure nicely onto the form. It also puts copied images in to the image box (which will be especially useful when cutting and pasting from other documents/sources other than chemdraw) A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. In my spreadsheet I have 10 different forms which the user calls up and each one will need to have a different chemical structure associated with and showing on it. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, it doesnt paste the selection into the form. Is there a way to do this as sometimes a user may elect to copy a structure from a pdf using the marquee tool in adobe reader and paste that (for example; if they dont have Chemdraw). Roger "Peter T" wrote: Have a go with the following. Put a button and and image control on a form. For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub '''' In a Normal Module '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' '' Based almost entirely on Stephen Bullen's "PastePicture.Zip" '' available from http://www.oaltd.co.uk '' Code below is copied from modPastePicture with headers and some '' comments removed. Function fnOLEError in the original module '' is also removed but shold be added back for completion. '' '' An additional new function - WhatsInClipboard() is included '' '' Recommend obtain the original module and include WhatsInClipboard '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Option Explicit Option Compare Text ''' User-Defined Types for API Calls 'Declare a UDT to store a GUID for the IPicture OLE Interface Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type 'Declare a UDT to store the bitmap information Private Type uPicDesc Size As Long Type As Long hPic As Long hPal As Long End Type '''Windows API Function Declarations 'Does the clipboard contain a bitmap/metafile? Private Declare Function IsClipboardFormatAvailable Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Open the clipboard to read Private Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd As Long) As Long 'Get a pointer to the bitmap/metafile Private Declare Function GetClipboardData Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Close the clipboard Private Declare Function CloseClipboard Lib "user32" () As Long 'Convert the handle into an OLE IPicture interface. Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" ( _ PicDesc As uPicDesc, RefIID As GUID, _ ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 'Create our own copy of the metafile, so it doesn't get _ 'wiped out by subsequent clipboard updates. Declare Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" ( _ ByVal hemfSrc As Long, ByVal lpszFile As String) As Long 'Create our own copy of the bitmap, so it doesn't get wiped out by subsequent 'clipboard updates. Declare Function CopyImage Lib "user32" (ByVal handle As Long, _ ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, _ ByVal un2 As Long) As Long 'The API format types we're interested in Const CF_BITMAP = 2 Const CF_PALETTE = 9 Const CF_ENHMETAFILE = 14 Const IMAGE_BITMAP = 0 Const LR_COPYRETURNORG = &H4 Function WhatsInClipboard() As Long ' This function is not included in PastePicture.zip ' Purpose is to learn which usable picture formats are in the ' clipboard, if any, before doing 'PastePicture' Dim hPicAvail As Long Dim picTypes As Long If IsClipboardFormatAvailable(CF_BITMAP) Then picTypes = 1 End If If IsClipboardFormatAvailable(CF_ENHMETAFILE) Then picTypes = picTypes Or 2 End If WhatsInClipboard = picTypes ' 1 got a bitmap ' 2 got a metafile ' 3 got both ' 0 got neither End Function Function PastePicture(Optional lXlPicType As Long = xlPicture) As IPicture 'Some pointers Dim h As Long, hPicAvail As Long, hPtr As Long, hPal As Long Dim lPicType As Long, hCopy As Long 'Convert xl piture-type constant to the API constant equivalent lPicType = IIf(lXlPicType = xlBitmap, CF_BITMAP, CF_ENHMETAFILE) 'Check if the clipboard contains the required format hPicAvail = IsClipboardFormatAvailable(lPicType) If hPicAvail < 0 Then 'Get access to the clipboard h = OpenClipboard(0&) If h 0 Then 'Get a handle to the image data hPtr = GetClipboardData(lPicType) 'Create our own copy of the image on the clipboard, in the appropriate format. If lPicType = CF_BITMAP Then hCopy = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG) Else hCopy = CopyEnhMetaFile(hPtr, vbNullString) End If 'Release the clipboard to other programs h = CloseClipboard 'If we got a handle to the image, convert it into a Picture object and return it If hPtr < 0 Then Set PastePicture = CreatePicture(hCopy, 0, lPicType) End If End If End Function Private Function CreatePicture(ByVal hPic As Long, ByVal hPal As Long, _ ByVal lPicType) As IPicture ' IPicture requires a reference to "OLE Automation" Dim r As Long, uPicInfo As uPicDesc, IID_IDispatch As GUID, IPic As IPicture 'OLE Picture types Const PICTYPE_BITMAP = 1 Const PICTYPE_ENHMETAFILE = 4 ' Create the Interface GUID (for the IPicture interface) With IID_IDispatch .Data1 = &H7BF80980 .Data2 = &HBF32 .Data3 = &H101A .Data4(0) = &H8B .Data4(1) = &HBB .Data4(2) = &H0 .Data4(3) = &HAA .Data4(4) = &H0 .Data4(5) = &H30 .Data4(6) = &HC .Data4(7) = &HAB End With ' Fill uPicInfo with necessary parts. With uPicInfo .Size = Len(uPicInfo) .Type = IIf(lPicType = CF_BITMAP, PICTYPE_BITMAP, PICTYPE_ENHMETAFILE) .hPic = hPic .hPal = IIf(lPicType = CF_BITMAP, hPal, 0) End With ' Create the Picture object. r = OleCreatePictureIndirect(uPicInfo, IID_IDispatch, True, IPic) ' If an error occured, show the description If r < 0 Then 'Debug.Print "Create Pictu " & fnOLEError(r) Debug.Print "Error, call fnOLEError(r) here" ' fnOLEError from modPastePicture not posted End If ' Return the new Picture object. Set CreatePicture = IPic End Function '' end code Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, The steps are as follows 1) User creates chemical structure picture in ChemDraw 2) user draws a selection box around structure and presses ctrlC 3) user opens excel file 4) user activates form 5)user pastes (ctrlV) structure into some sort of dialog box/text box/other control?? on the form- such a control may not exist 6) Excel appends the copied image as a picture onto another predefined user form (or the same form) 7) when user opens the predefined userform the pasted image appears on the form Not sure if I can describe this any other way apart from the fact that my spreadsheet is effectively run using user forms with data displayed on userforms. I am trying to get away from the uder having to paste pictures directly into the sheets themselves since the spreadsheet is driven by userforms Best regards, Roger "Roger on Excel" wrote: I have userforms which I activate to show text information. I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window.
Code in the userform code as posted previously is incorrect (in the scenario
of both bitmap and metafile image formats in the clipboard). Change the posted CommandButton1_Click() code as follows Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture ElseIf lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub Peter T "Peter T" <peter_t@discussions wrote in message ... Have a go with the following. Put a button and and image control on a form. For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub '''' In a Normal Module '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' '' Based almost entirely on Stephen Bullen's "PastePicture.Zip" '' available from http://www.oaltd.co.uk '' Code below is copied from modPastePicture with headers and some '' comments removed. Function fnOLEError in the original module '' is also removed but shold be added back for completion. '' '' An additional new function - WhatsInClipboard() is included '' '' Recommend obtain the original module and include WhatsInClipboard '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Option Explicit Option Compare Text ''' User-Defined Types for API Calls 'Declare a UDT to store a GUID for the IPicture OLE Interface Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type 'Declare a UDT to store the bitmap information Private Type uPicDesc Size As Long Type As Long hPic As Long hPal As Long End Type '''Windows API Function Declarations 'Does the clipboard contain a bitmap/metafile? Private Declare Function IsClipboardFormatAvailable Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Open the clipboard to read Private Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd As Long) As Long 'Get a pointer to the bitmap/metafile Private Declare Function GetClipboardData Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Close the clipboard Private Declare Function CloseClipboard Lib "user32" () As Long 'Convert the handle into an OLE IPicture interface. Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" ( _ PicDesc As uPicDesc, RefIID As GUID, _ ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 'Create our own copy of the metafile, so it doesn't get _ 'wiped out by subsequent clipboard updates. Declare Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" ( _ ByVal hemfSrc As Long, ByVal lpszFile As String) As Long 'Create our own copy of the bitmap, so it doesn't get wiped out by subsequent 'clipboard updates. Declare Function CopyImage Lib "user32" (ByVal handle As Long, _ ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, _ ByVal un2 As Long) As Long 'The API format types we're interested in Const CF_BITMAP = 2 Const CF_PALETTE = 9 Const CF_ENHMETAFILE = 14 Const IMAGE_BITMAP = 0 Const LR_COPYRETURNORG = &H4 Function WhatsInClipboard() As Long ' This function is not included in PastePicture.zip ' Purpose is to learn which usable picture formats are in the ' clipboard, if any, before doing 'PastePicture' Dim hPicAvail As Long Dim picTypes As Long If IsClipboardFormatAvailable(CF_BITMAP) Then picTypes = 1 End If If IsClipboardFormatAvailable(CF_ENHMETAFILE) Then picTypes = picTypes Or 2 End If WhatsInClipboard = picTypes ' 1 got a bitmap ' 2 got a metafile ' 3 got both ' 0 got neither End Function Function PastePicture(Optional lXlPicType As Long = xlPicture) As IPicture 'Some pointers Dim h As Long, hPicAvail As Long, hPtr As Long, hPal As Long Dim lPicType As Long, hCopy As Long 'Convert xl piture-type constant to the API constant equivalent lPicType = IIf(lXlPicType = xlBitmap, CF_BITMAP, CF_ENHMETAFILE) 'Check if the clipboard contains the required format hPicAvail = IsClipboardFormatAvailable(lPicType) If hPicAvail < 0 Then 'Get access to the clipboard h = OpenClipboard(0&) If h 0 Then 'Get a handle to the image data hPtr = GetClipboardData(lPicType) 'Create our own copy of the image on the clipboard, in the appropriate format. If lPicType = CF_BITMAP Then hCopy = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG) Else hCopy = CopyEnhMetaFile(hPtr, vbNullString) End If 'Release the clipboard to other programs h = CloseClipboard 'If we got a handle to the image, convert it into a Picture object and return it If hPtr < 0 Then Set PastePicture = CreatePicture(hCopy, 0, lPicType) End If End If End Function Private Function CreatePicture(ByVal hPic As Long, ByVal hPal As Long, _ ByVal lPicType) As IPicture ' IPicture requires a reference to "OLE Automation" Dim r As Long, uPicInfo As uPicDesc, IID_IDispatch As GUID, IPic As IPicture 'OLE Picture types Const PICTYPE_BITMAP = 1 Const PICTYPE_ENHMETAFILE = 4 ' Create the Interface GUID (for the IPicture interface) With IID_IDispatch .Data1 = &H7BF80980 .Data2 = &HBF32 .Data3 = &H101A .Data4(0) = &H8B .Data4(1) = &HBB .Data4(2) = &H0 .Data4(3) = &HAA .Data4(4) = &H0 .Data4(5) = &H30 .Data4(6) = &HC .Data4(7) = &HAB End With ' Fill uPicInfo with necessary parts. With uPicInfo .Size = Len(uPicInfo) .Type = IIf(lPicType = CF_BITMAP, PICTYPE_BITMAP, PICTYPE_ENHMETAFILE) .hPic = hPic .hPal = IIf(lPicType = CF_BITMAP, hPal, 0) End With ' Create the Picture object. r = OleCreatePictureIndirect(uPicInfo, IID_IDispatch, True, IPic) ' If an error occured, show the description If r < 0 Then 'Debug.Print "Create Pictu " & fnOLEError(r) Debug.Print "Error, call fnOLEError(r) here" ' fnOLEError from modPastePicture not posted End If ' Return the new Picture object. Set CreatePicture = IPic End Function '' end code Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, The steps are as follows 1) User creates chemical structure picture in ChemDraw 2) user draws a selection box around structure and presses ctrlC 3) user opens excel file 4) user activates form 5)user pastes (ctrlV) structure into some sort of dialog box/text box/other control?? on the form- such a control may not exist 6) Excel appends the copied image as a picture onto another predefined user form (or the same form) 7) when user opens the predefined userform the pasted image appears on the form Not sure if I can describe this any other way apart from the fact that my spreadsheet is effectively run using user forms with data displayed on userforms. I am trying to get away from the uder having to paste pictures directly into the sheets themselves since the spreadsheet is driven by userforms Best regards, Roger "Roger on Excel" wrote: I have userforms which I activate to show text information. I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting the image into the picture line of the Image Properties box. This is too complicated for a user of my spreadsheet to do so I am looking for alternative way of pasting an image so that it becomes attached to a userform and shows when the user opens the form. Can anyone help? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
Roger,
If your users have ChemDraw then you likely also have the ChemDraw ActiveX control, which you should be able to host on an Excel form, allowing your users to paste directly into it (not to mention it gives you other useful capabilities...) Tim "Roger on Excel" wrote in message ... Hi Peter, Many thanks for helping me on this problem. I tried your code out and it works very very nicely - It puts a ChemDraw structure nicely onto the form. It also puts copied images in to the image box (which will be especially useful when cutting and pasting from other documents/sources other than chemdraw) A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. In my spreadsheet I have 10 different forms which the user calls up and each one will need to have a different chemical structure associated with and showing on it. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, it doesnt paste the selection into the form. Is there a way to do this as sometimes a user may elect to copy a structure from a pdf using the marquee tool in adobe reader and paste that (for example; if they dont have Chemdraw). Roger "Peter T" wrote: Have a go with the following. Put a button and and image control on a form. For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub '''' In a Normal Module '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' '' Based almost entirely on Stephen Bullen's "PastePicture.Zip" '' available from http://www.oaltd.co.uk '' Code below is copied from modPastePicture with headers and some '' comments removed. Function fnOLEError in the original module '' is also removed but shold be added back for completion. '' '' An additional new function - WhatsInClipboard() is included '' '' Recommend obtain the original module and include WhatsInClipboard '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Option Explicit Option Compare Text ''' User-Defined Types for API Calls 'Declare a UDT to store a GUID for the IPicture OLE Interface Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type 'Declare a UDT to store the bitmap information Private Type uPicDesc Size As Long Type As Long hPic As Long hPal As Long End Type '''Windows API Function Declarations 'Does the clipboard contain a bitmap/metafile? Private Declare Function IsClipboardFormatAvailable Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Open the clipboard to read Private Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd As Long) As Long 'Get a pointer to the bitmap/metafile Private Declare Function GetClipboardData Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Close the clipboard Private Declare Function CloseClipboard Lib "user32" () As Long 'Convert the handle into an OLE IPicture interface. Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" ( _ PicDesc As uPicDesc, RefIID As GUID, _ ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 'Create our own copy of the metafile, so it doesn't get _ 'wiped out by subsequent clipboard updates. Declare Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" ( _ ByVal hemfSrc As Long, ByVal lpszFile As String) As Long 'Create our own copy of the bitmap, so it doesn't get wiped out by subsequent 'clipboard updates. Declare Function CopyImage Lib "user32" (ByVal handle As Long, _ ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, _ ByVal un2 As Long) As Long 'The API format types we're interested in Const CF_BITMAP = 2 Const CF_PALETTE = 9 Const CF_ENHMETAFILE = 14 Const IMAGE_BITMAP = 0 Const LR_COPYRETURNORG = &H4 Function WhatsInClipboard() As Long ' This function is not included in PastePicture.zip ' Purpose is to learn which usable picture formats are in the ' clipboard, if any, before doing 'PastePicture' Dim hPicAvail As Long Dim picTypes As Long If IsClipboardFormatAvailable(CF_BITMAP) Then picTypes = 1 End If If IsClipboardFormatAvailable(CF_ENHMETAFILE) Then picTypes = picTypes Or 2 End If WhatsInClipboard = picTypes ' 1 got a bitmap ' 2 got a metafile ' 3 got both ' 0 got neither End Function Function PastePicture(Optional lXlPicType As Long = xlPicture) As IPicture 'Some pointers Dim h As Long, hPicAvail As Long, hPtr As Long, hPal As Long Dim lPicType As Long, hCopy As Long 'Convert xl piture-type constant to the API constant equivalent lPicType = IIf(lXlPicType = xlBitmap, CF_BITMAP, CF_ENHMETAFILE) 'Check if the clipboard contains the required format hPicAvail = IsClipboardFormatAvailable(lPicType) If hPicAvail < 0 Then 'Get access to the clipboard h = OpenClipboard(0&) If h 0 Then 'Get a handle to the image data hPtr = GetClipboardData(lPicType) 'Create our own copy of the image on the clipboard, in the appropriate format. If lPicType = CF_BITMAP Then hCopy = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG) Else hCopy = CopyEnhMetaFile(hPtr, vbNullString) End If 'Release the clipboard to other programs h = CloseClipboard 'If we got a handle to the image, convert it into a Picture object and return it If hPtr < 0 Then Set PastePicture = CreatePicture(hCopy, 0, lPicType) End If End If End Function Private Function CreatePicture(ByVal hPic As Long, ByVal hPal As Long, _ ByVal lPicType) As IPicture ' IPicture requires a reference to "OLE Automation" Dim r As Long, uPicInfo As uPicDesc, IID_IDispatch As GUID, IPic As IPicture 'OLE Picture types Const PICTYPE_BITMAP = 1 Const PICTYPE_ENHMETAFILE = 4 ' Create the Interface GUID (for the IPicture interface) With IID_IDispatch .Data1 = &H7BF80980 .Data2 = &HBF32 .Data3 = &H101A .Data4(0) = &H8B .Data4(1) = &HBB .Data4(2) = &H0 .Data4(3) = &HAA .Data4(4) = &H0 .Data4(5) = &H30 .Data4(6) = &HC .Data4(7) = &HAB End With ' Fill uPicInfo with necessary parts. With uPicInfo .Size = Len(uPicInfo) .Type = IIf(lPicType = CF_BITMAP, PICTYPE_BITMAP, PICTYPE_ENHMETAFILE) .hPic = hPic .hPal = IIf(lPicType = CF_BITMAP, hPal, 0) End With ' Create the Picture object. r = OleCreatePictureIndirect(uPicInfo, IID_IDispatch, True, IPic) ' If an error occured, show the description If r < 0 Then 'Debug.Print "Create Pictu " & fnOLEError(r) Debug.Print "Error, call fnOLEError(r) here" ' fnOLEError from modPastePicture not posted End If ' Return the new Picture object. Set CreatePicture = IPic End Function '' end code Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, The steps are as follows 1) User creates chemical structure picture in ChemDraw 2) user draws a selection box around structure and presses ctrlC 3) user opens excel file 4) user activates form 5)user pastes (ctrlV) structure into some sort of dialog box/text box/other control?? on the form- such a control may not exist 6) Excel appends the copied image as a picture onto another predefined user form (or the same form) 7) when user opens the predefined userform the pasted image appears on the form Not sure if I can describe this any other way apart from the fact that my spreadsheet is effectively run using user forms with data displayed on userforms. I am trying to get away from the uder having to paste pictures directly into the sheets themselves since the spreadsheet is driven by userforms Best regards, Roger "Roger on Excel" wrote: I have userforms which I activate to show text information. I would like to be able to paste a picture from the clipboard onto an opened userform so that there is a picture as well as text. The picture would be a copied picture from the web or a copied wmf from another application Traditionally the way to do this is through the vba window by using the image control and navigating it to a saved image or alternatively pasting |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
Dear Peter,
Its looking really good - i was able to copy the code and have two forms with different pictures on each. However, could you describe how to save the changes in a little more detail as I would need the user to be able to save their changes from session to session? I pasted the following into ThisWorkbook ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture But it said that it was invalid outside a procedure - is there some other code needed? Roger "Peter T" wrote: Glad it seems to be working, after all that! A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. If the requirement is only for the session, in the form - Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = True Me.Hide End If One way to permanently store the image would be to an ActiveX Image control (hidden) in a sheet, eg ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture reverse to load back to the form's image control Another way would be to save to file and load from file. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, Oops, my fault, not sure how such an elementary error crept in! In the userform code (as posted last time) replace CommandButton1_Click with the following Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture ElseIf lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub Trust you've got the image control's picture properties adjusted to needs, either at design or amended at runtime.. FWIW, if you want to know the overall dimensions of the image before showing it (first make invisible), allow the image to Autosize, get the new dim's, resize to original or other as required, or leave as autosize'd Regards, Peter T "Roger on Excel" wrote in message ... Hi Peter, Many thanks for helping me on this problem. I tried your code out and it works very very nicely - It puts a ChemDraw structure nicely onto the form. It also puts copied images in to the image box (which will be especially useful when cutting and pasting from other documents/sources other than chemdraw) A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. In my spreadsheet I have 10 different forms which the user calls up and each one will need to have a different chemical structure associated with and showing on it. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, it doesnt paste the selection into the form. Is there a way to do this as sometimes a user may elect to copy a structure from a pdf using the marquee tool in adobe reader and paste that (for example; if they dont have Chemdraw). Roger "Peter T" wrote: Have a go with the following. Put a button and and image control on a form. For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub '''' In a Normal Module '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' '' Based almost entirely on Stephen Bullen's "PastePicture.Zip" '' available from http://www.oaltd.co.uk '' Code below is copied from modPastePicture with headers and some '' comments removed. Function fnOLEError in the original module '' is also removed but shold be added back for completion. '' '' An additional new function - WhatsInClipboard() is included '' '' Recommend obtain the original module and include WhatsInClipboard '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Option Explicit Option Compare Text ''' User-Defined Types for API Calls 'Declare a UDT to store a GUID for the IPicture OLE Interface Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type 'Declare a UDT to store the bitmap information Private Type uPicDesc Size As Long Type As Long hPic As Long hPal As Long End Type '''Windows API Function Declarations 'Does the clipboard contain a bitmap/metafile? Private Declare Function IsClipboardFormatAvailable Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Open the clipboard to read Private Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd As Long) As Long 'Get a pointer to the bitmap/metafile Private Declare Function GetClipboardData Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Close the clipboard Private Declare Function CloseClipboard Lib "user32" () As Long 'Convert the handle into an OLE IPicture interface. Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" ( _ PicDesc As uPicDesc, RefIID As GUID, _ ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 'Create our own copy of the metafile, so it doesn't get _ 'wiped out by subsequent clipboard updates. Declare Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" ( _ ByVal hemfSrc As Long, ByVal lpszFile As String) As Long 'Create our own copy of the bitmap, so it doesn't get wiped out by subsequent 'clipboard updates. Declare Function CopyImage Lib "user32" (ByVal handle As Long, _ ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, _ ByVal un2 As Long) As Long 'The API format types we're interested in Const CF_BITMAP = 2 Const CF_PALETTE = 9 Const CF_ENHMETAFILE = 14 Const IMAGE_BITMAP = 0 Const LR_COPYRETURNORG = &H4 Function WhatsInClipboard() As Long ' This function is not included in PastePicture.zip ' Purpose is to learn which usable picture formats are in the ' clipboard, if any, before doing 'PastePicture' Dim hPicAvail As Long Dim picTypes As Long If IsClipboardFormatAvailable(CF_BITMAP) Then picTypes = 1 End If If IsClipboardFormatAvailable(CF_ENHMETAFILE) Then picTypes = picTypes Or 2 End If WhatsInClipboard = picTypes ' 1 got a bitmap ' 2 got a metafile ' 3 got both ' 0 got neither End Function Function PastePicture(Optional lXlPicType As Long = xlPicture) As IPicture 'Some pointers Dim h As Long, hPicAvail As Long, hPtr As Long, hPal As Long Dim lPicType As Long, hCopy As Long 'Convert xl piture-type constant to the API constant equivalent lPicType = IIf(lXlPicType = xlBitmap, CF_BITMAP, CF_ENHMETAFILE) 'Check if the clipboard contains the required format hPicAvail = IsClipboardFormatAvailable(lPicType) If hPicAvail < 0 Then 'Get access to the clipboard h = OpenClipboard(0&) If h 0 Then 'Get a handle to the image data hPtr = GetClipboardData(lPicType) 'Create our own copy of the image on the clipboard, in the appropriate format. If lPicType = CF_BITMAP Then hCopy = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG) Else hCopy = CopyEnhMetaFile(hPtr, vbNullString) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
Put an ActiveX Image Control on a Worksheet (say on "Sheet1") in the same
workbook as the userform ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture This is simply a single line of code that needs to go in an appropeiate routine. It's NOT an event stub that belongs in the ThisWorkbook module. In this context "ThisWorkbook" refers to the workbook that contans the running the code. You can change ThisWorkbook to any reference that refers to some workbook, eg a hidden workbook in which you want to store pictures (put loads of image controls in it). For testing paste the above line immediately after Me.Image1.Picture = PastePicture(lXlPicType) but it can go in any routine you choose to put it in. Alternatively, as I mentioned earlier, you could save the image to file (obviously you'd need to manage file names). Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, Its looking really good - i was able to copy the code and have two forms with different pictures on each. However, could you describe how to save the changes in a little more detail as I would need the user to be able to save their changes from session to session? I pasted the following into ThisWorkbook ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture But it said that it was invalid outside a procedure - is there some other code needed? Roger "Peter T" wrote: Glad it seems to be working, after all that! A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. If the requirement is only for the session, in the form - Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = True Me.Hide End If One way to permanently store the image would be to an ActiveX Image control (hidden) in a sheet, eg ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture reverse to load back to the form's image control Another way would be to save to file and load from file. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, Oops, my fault, not sure how such an elementary error crept in! In the userform code (as posted last time) replace CommandButton1_Click with the following Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture ElseIf lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub Trust you've got the image control's picture properties adjusted to needs, either at design or amended at runtime.. FWIW, if you want to know the overall dimensions of the image before showing it (first make invisible), allow the image to Autosize, get the new dim's, resize to original or other as required, or leave as autosize'd Regards, Peter T "Roger on Excel" wrote in message ... Hi Peter, Many thanks for helping me on this problem. I tried your code out and it works very very nicely - It puts a ChemDraw structure nicely onto the form. It also puts copied images in to the image box (which will be especially useful when cutting and pasting from other documents/sources other than chemdraw) A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. In my spreadsheet I have 10 different forms which the user calls up and each one will need to have a different chemical structure associated with and showing on it. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, it doesnt paste the selection into the form. Is there a way to do this as sometimes a user may elect to copy a structure from a pdf using the marquee tool in adobe reader and paste that (for example; if they dont have Chemdraw). Roger "Peter T" wrote: Have a go with the following. Put a button and and image control on a form. For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub '''' In a Normal Module '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' '' Based almost entirely on Stephen Bullen's "PastePicture.Zip" '' available from http://www.oaltd.co.uk '' Code below is copied from modPastePicture with headers and some '' comments removed. Function fnOLEError in the original module '' is also removed but shold be added back for completion. '' '' An additional new function - WhatsInClipboard() is included '' '' Recommend obtain the original module and include WhatsInClipboard '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Option Explicit Option Compare Text ''' User-Defined Types for API Calls 'Declare a UDT to store a GUID for the IPicture OLE Interface Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type 'Declare a UDT to store the bitmap information Private Type uPicDesc Size As Long Type As Long hPic As Long hPal As Long End Type '''Windows API Function Declarations 'Does the clipboard contain a bitmap/metafile? Private Declare Function IsClipboardFormatAvailable Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Open the clipboard to read Private Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd As Long) As Long 'Get a pointer to the bitmap/metafile Private Declare Function GetClipboardData Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Close the clipboard Private Declare Function CloseClipboard Lib "user32" () As Long 'Convert the handle into an OLE IPicture interface. Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" ( _ PicDesc As uPicDesc, RefIID As GUID, _ ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 'Create our own copy of the metafile, so it doesn't get _ 'wiped out by subsequent clipboard updates. Declare Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" ( _ ByVal hemfSrc As Long, ByVal lpszFile As String) As Long 'Create our own copy of the bitmap, so it doesn't get wiped out by subsequent 'clipboard updates. Declare Function CopyImage Lib "user32" (ByVal handle As Long, _ ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, _ ByVal un2 As Long) As Long 'The API format types we're interested in Const CF_BITMAP = 2 Const CF_PALETTE = 9 Const CF_ENHMETAFILE = 14 Const IMAGE_BITMAP = 0 Const LR_COPYRETURNORG = &H4 Function WhatsInClipboard() As Long ' This function is not included in PastePicture.zip ' Purpose is to learn which usable picture formats are in the ' clipboard, if any, before doing 'PastePicture' Dim hPicAvail As Long Dim picTypes As Long If IsClipboardFormatAvailable(CF_BITMAP) Then picTypes = 1 End If If IsClipboardFormatAvailable(CF_ENHMETAFILE) Then picTypes = picTypes Or 2 End If WhatsInClipboard = picTypes ' 1 got a bitmap ' 2 got a metafile ' 3 got both ' 0 got neither End Function Function PastePicture(Optional lXlPicType As Long = xlPicture) As IPicture 'Some pointers Dim h As Long, hPicAvail As Long, hPtr As Long, hPal As Long Dim lPicType As Long, hCopy As Long 'Convert xl piture-type constant to the API constant equivalent lPicType = IIf(lXlPicType = xlBitmap, CF_BITMAP, CF_ENHMETAFILE) 'Check if the clipboard contains the required format hPicAvail = IsClipboardFormatAvailable(lPicType) If hPicAvail < 0 Then 'Get access to the clipboard h = OpenClipboard(0&) If h 0 Then 'Get a handle to the image data hPtr = GetClipboardData(lPicType) 'Create our own copy of the image on the clipboard, in the appropriate format. If lPicType = CF_BITMAP Then hCopy = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG) Else hCopy = CopyEnhMetaFile(hPtr, vbNullString) |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
.....also
I don't know ChemDraw but from what Tim says it has an ActiveX control that you might be able to use either on a form or on a sheet, or on both even. On a form - right click the Toolbox, Additional controls, if you see it in the list click the little x which will add it to the toolbox. Slightly different for worksheet controls, again rt-click the toolbox, find the control in the list and select. That won't put it on the toolbox, instead immediately try drawing it on the sheet. In design mode, double click it. Whether on the form or sheet a reference might get automatically added. If not you might be able to add it in tools/references. If (big if) you can access the ChemDraw control you may be able to use it as you would in the main app, and further you might be able to code to it directly. Maybe it also exposes events. Worth checking out. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Put an ActiveX Image Control on a Worksheet (say on "Sheet1") in the same workbook as the userform ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture This is simply a single line of code that needs to go in an appropeiate routine. It's NOT an event stub that belongs in the ThisWorkbook module. In this context "ThisWorkbook" refers to the workbook that contans the running the code. You can change ThisWorkbook to any reference that refers to some workbook, eg a hidden workbook in which you want to store pictures (put loads of image controls in it). For testing paste the above line immediately after Me.Image1.Picture = PastePicture(lXlPicType) but it can go in any routine you choose to put it in. Alternatively, as I mentioned earlier, you could save the image to file (obviously you'd need to manage file names). Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, Its looking really good - i was able to copy the code and have two forms with different pictures on each. However, could you describe how to save the changes in a little more detail as I would need the user to be able to save their changes from session to session? I pasted the following into ThisWorkbook ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture But it said that it was invalid outside a procedure - is there some other code needed? Roger "Peter T" wrote: Glad it seems to be working, after all that! A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. If the requirement is only for the session, in the form - Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = True Me.Hide End If One way to permanently store the image would be to an ActiveX Image control (hidden) in a sheet, eg ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture reverse to load back to the form's image control Another way would be to save to file and load from file. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, Oops, my fault, not sure how such an elementary error crept in! In the userform code (as posted last time) replace CommandButton1_Click with the following Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture ElseIf lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub Trust you've got the image control's picture properties adjusted to needs, either at design or amended at runtime.. FWIW, if you want to know the overall dimensions of the image before showing it (first make invisible), allow the image to Autosize, get the new dim's, resize to original or other as required, or leave as autosize'd Regards, Peter T "Roger on Excel" wrote in message ... Hi Peter, Many thanks for helping me on this problem. I tried your code out and it works very very nicely - It puts a ChemDraw structure nicely onto the form. It also puts copied images in to the image box (which will be especially useful when cutting and pasting from other documents/sources other than chemdraw) A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. In my spreadsheet I have 10 different forms which the user calls up and each one will need to have a different chemical structure associated with and showing on it. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, it doesnt paste the selection into the form. Is there a way to do this as sometimes a user may elect to copy a structure from a pdf using the marquee tool in adobe reader and paste that (for example; if they dont have Chemdraw). Roger "Peter T" wrote: Have a go with the following. Put a button and and image control on a form. For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub '''' In a Normal Module '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' '' Based almost entirely on Stephen Bullen's "PastePicture.Zip" '' available from http://www.oaltd.co.uk '' Code below is copied from modPastePicture with headers and some '' comments removed. Function fnOLEError in the original module '' is also removed but shold be added back for completion. '' '' An additional new function - WhatsInClipboard() is included '' '' Recommend obtain the original module and include WhatsInClipboard '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Option Explicit Option Compare Text ''' User-Defined Types for API Calls 'Declare a UDT to store a GUID for the IPicture OLE Interface Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type 'Declare a UDT to store the bitmap information Private Type uPicDesc Size As Long Type As Long hPic As Long hPal As Long End Type '''Windows API Function Declarations 'Does the clipboard contain a bitmap/metafile? Private Declare Function IsClipboardFormatAvailable Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Open the clipboard to read Private Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd As Long) As Long 'Get a pointer to the bitmap/metafile Private Declare Function GetClipboardData Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Close the clipboard Private Declare Function CloseClipboard Lib "user32" () As Long 'Convert the handle into an OLE IPicture interface. Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" ( _ PicDesc As uPicDesc, RefIID As GUID, _ ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 'Create our own copy of the metafile, so it doesn't get _ 'wiped out by subsequent clipboard updates. Declare Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" ( _ ByVal hemfSrc As Long, ByVal lpszFile As String) As Long 'Create our own copy of the bitmap, so it doesn't get wiped out by subsequent 'clipboard updates. Declare Function CopyImage Lib "user32" (ByVal handle As Long, _ ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, _ ByVal un2 As Long) As Long 'The API format types we're interested in Const CF_BITMAP = 2 Const CF_PALETTE = 9 Const CF_ENHMETAFILE = 14 Const IMAGE_BITMAP = 0 Const LR_COPYRETURNORG = &H4 Function WhatsInClipboard() As Long ' This function is not included in PastePicture.zip ' Purpose is to learn which usable picture formats are in the ' clipboard, if any, before doing 'PastePicture' Dim hPicAvail As Long Dim picTypes As Long If IsClipboardFormatAvailable(CF_BITMAP) Then picTypes = 1 End If If IsClipboardFormatAvailable(CF_ENHMETAFILE) Then picTypes = picTypes Or 2 End If WhatsInClipboard = picTypes ' 1 got a bitmap ' 2 got a metafile ' 3 got both ' 0 got neither End Function Function PastePicture(Optional lXlPicType As Long = xlPicture) As IPicture 'Some pointers Dim h As Long, hPicAvail As Long, hPtr As Long, hPal As Long Dim lPicType As Long, hCopy As Long 'Convert xl piture-type constant to the API constant equivalent lPicType = IIf(lXlPicType = xlBitmap, CF_BITMAP, CF_ENHMETAFILE) 'Check if the clipboard contains the required format hPicAvail = IsClipboardFormatAvailable(lPicType) If hPicAvail < 0 Then 'Get access to the clipboard h = OpenClipboard(0&) If h 0 Then 'Get a handle to the image data hPtr = GetClipboardData(lPicType) 'Create our own copy of the image on the clipboard, in the appropriate format. If lPicType = CF_BITMAP Then hCopy = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG) Else hCopy = CopyEnhMetaFile(hPtr, vbNullString) |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
I've sent you a demo
Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, I am sorry, but I seem to be struggling with this last part of the puzzle making the pictures stay with the file when I close it. I would appreciate it if you could step me through these last stages in a little more detail as I do not know where or how to put an activex control onto an actual sheet. and I am struggling with the code for this last part. (Also I have twin babies to look after) I would need any attached form images to be portable with the saved excel file and be openable from any pc. (I dont have Chemdraw installed on my home pc). Feel free to send me a file if you wish to my email as you have been so very helpful All the best, Roger "Peter T" wrote: Put an ActiveX Image Control on a Worksheet (say on "Sheet1") in the same workbook as the userform ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture This is simply a single line of code that needs to go in an appropeiate routine. It's NOT an event stub that belongs in the ThisWorkbook module. In this context "ThisWorkbook" refers to the workbook that contans the running the code. You can change ThisWorkbook to any reference that refers to some workbook, eg a hidden workbook in which you want to store pictures (put loads of image controls in it). For testing paste the above line immediately after Me.Image1.Picture = PastePicture(lXlPicType) but it can go in any routine you choose to put it in. Alternatively, as I mentioned earlier, you could save the image to file (obviously you'd need to manage file names). Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, Its looking really good - i was able to copy the code and have two forms with different pictures on each. However, could you describe how to save the changes in a little more detail as I would need the user to be able to save their changes from session to session? I pasted the following into ThisWorkbook ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture But it said that it was invalid outside a procedure - is there some other code needed? Roger "Peter T" wrote: Glad it seems to be working, after all that! A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. If the requirement is only for the session, in the form - Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = True Me.Hide End If One way to permanently store the image would be to an ActiveX Image control (hidden) in a sheet, eg ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture reverse to load back to the form's image control Another way would be to save to file and load from file. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, Oops, my fault, not sure how such an elementary error crept in! In the userform code (as posted last time) replace CommandButton1_Click with the following Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture ElseIf lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub Trust you've got the image control's picture properties adjusted to needs, either at design or amended at runtime.. FWIW, if you want to know the overall dimensions of the image before showing it (first make invisible), allow the image to Autosize, get the new dim's, resize to original or other as required, or leave as autosize'd Regards, Peter T "Roger on Excel" wrote in message ... Hi Peter, Many thanks for helping me on this problem. I tried your code out and it works very very nicely - It puts a ChemDraw structure nicely onto the form. It also puts copied images in to the image box (which will be especially useful when cutting and pasting from other documents/sources other than chemdraw) A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. In my spreadsheet I have 10 different forms which the user calls up and each one will need to have a different chemical structure associated with and showing on it. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, it doesnt paste the selection into the form. Is there a way to do this as sometimes a user may elect to copy a structure from a pdf using the marquee tool in adobe reader and paste that (for example; if they dont have Chemdraw). Roger "Peter T" wrote: Have a go with the following. Put a button and and image control on a form. For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub '''' In a Normal Module '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''' '' Based almost entirely on Stephen Bullen's "PastePicture.Zip" '' available from http://www.oaltd.co.uk '' Code below is copied from modPastePicture with headers and some '' comments removed. Function fnOLEError in the original module '' is also removed but shold be added back for completion. '' '' An additional new function - WhatsInClipboard() is included '' '' Recommend obtain the original module and include WhatsInClipboard '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' Option Explicit Option Compare Text ''' User-Defined Types for API Calls 'Declare a UDT to store a GUID for the IPicture OLE Interface Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type 'Declare a UDT to store the bitmap information Private Type uPicDesc Size As Long Type As Long hPic As Long hPal As Long End Type '''Windows API Function Declarations 'Does the clipboard contain a bitmap/metafile? Private Declare Function IsClipboardFormatAvailable Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Open the clipboard to read Private Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd As Long) As Long 'Get a pointer to the bitmap/metafile Private Declare Function GetClipboardData Lib "user32" ( _ ByVal wFormat As Integer) As Long 'Close the clipboard Private Declare Function CloseClipboard Lib "user32" () As Long 'Convert the handle into an OLE IPicture interface. Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" ( _ |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
Hi Peter,
Thanks again for all the help - your suggestions and code worked great. All the best, Roger "Peter T" wrote: I've sent you a demo Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, I am sorry, but I seem to be struggling with this last part of the puzzle making the pictures stay with the file when I close it. I would appreciate it if you could step me through these last stages in a little more detail as I do not know where or how to put an activex control onto an actual sheet. and I am struggling with the code for this last part. (Also I have twin babies to look after) I would need any attached form images to be portable with the saved excel file and be openable from any pc. (I dont have Chemdraw installed on my home pc). Feel free to send me a file if you wish to my email as you have been so very helpful All the best, Roger "Peter T" wrote: Put an ActiveX Image Control on a Worksheet (say on "Sheet1") in the same workbook as the userform ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture This is simply a single line of code that needs to go in an appropeiate routine. It's NOT an event stub that belongs in the ThisWorkbook module. In this context "ThisWorkbook" refers to the workbook that contans the running the code. You can change ThisWorkbook to any reference that refers to some workbook, eg a hidden workbook in which you want to store pictures (put loads of image controls in it). For testing paste the above line immediately after Me.Image1.Picture = PastePicture(lXlPicType) but it can go in any routine you choose to put it in. Alternatively, as I mentioned earlier, you could save the image to file (obviously you'd need to manage file names). Regards, Peter T "Roger on Excel" wrote in message ... Dear Peter, Its looking really good - i was able to copy the code and have two forms with different pictures on each. However, could you describe how to save the changes in a little more detail as I would need the user to be able to save their changes from session to session? I pasted the following into ThisWorkbook ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture But it said that it was invalid outside a procedure - is there some other code needed? Roger "Peter T" wrote: Glad it seems to be working, after all that! A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. If the requirement is only for the session, in the form - Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = True Me.Hide End If One way to permanently store the image would be to an ActiveX Image control (hidden) in a sheet, eg ThisWorkbook.Worksheets("Sheet1").OLEObjects("Imag e1").Object.Picture = _ Me.Image1.Picture reverse to load back to the form's image control Another way would be to save to file and load from file. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, Oops, my fault, not sure how such an elementary error crept in! In the userform code (as posted last time) replace CommandButton1_Click with the following Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture ElseIf lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If Else MsgBox "No picture on clipboard" Exit Sub End If Me.Image1.Picture = PastePicture(lXlPicType) End Sub Trust you've got the image control's picture properties adjusted to needs, either at design or amended at runtime.. FWIW, if you want to know the overall dimensions of the image before showing it (first make invisible), allow the image to Autosize, get the new dim's, resize to original or other as required, or leave as autosize'd Regards, Peter T "Roger on Excel" wrote in message ... Hi Peter, Many thanks for helping me on this problem. I tried your code out and it works very very nicely - It puts a ChemDraw structure nicely onto the form. It also puts copied images in to the image box (which will be especially useful when cutting and pasting from other documents/sources other than chemdraw) A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. In my spreadsheet I have 10 different forms which the user calls up and each one will need to have a different chemical structure associated with and showing on it. I also noticed that if I draw a selection box in a pdf and copy the selection to the clipboard, it doesnt paste the selection into the form. Is there a way to do this as sometimes a user may elect to copy a structure from a pdf using the marquee tool in adobe reader and paste that (for example; if they dont have Chemdraw). Roger "Peter T" wrote: Have a go with the following. Put a button and and image control on a form. For testing suggest show the form modeless Sub ShowForm() UserForm1.Show vbModeless End Sub Run the form, activate Excel, select some cells, (in Excel97-2003) hold Shift and select Edit - CopyPicture, try both Picture and Bitmap. In Excel2007 click the arrow below Paste, As picture, Copy Picture (but note emf/picture does not work correctly in 2007 due a bug). For your eventual purposes I'm a bit concerned about your overal arrangement, in particular how do you know user has copied a suitable picture before opening your workbook (which could destroy the clipboard depending on other factors). To at least get some idea I've added an additional function WhatsInClipboard (see code) which will at least tell you a picture is available, although not if it is an appropriate one. I suspect you will only want 'bitmap' but if your ChemDraw app supports it the 'metafile' may give a better result. Either way eventually you probably won't want to be asking the user what type (as below). Obviously if you want the image to automatically appear when the form loads, call the PastePicture from the form's initialize event (be sure to pass the intended picture type). Finally, you will probably want to tinker with the Image controls picture properties, either at design or during runtime (see comments). '' In a UserForm with CommandButton1 and Image1 '' the image control's Autosize, PictureAlignment and PictureSizeMode '' can be fixed at design or during runtime Private Sub CommandButton1_Click() Dim lPicType As Long, lXlPicType As Long lPicType = WhatsInClipboard If lPicType = 1 Then lXlPicType = xlBitmap ElseIf lPicType = 2 Then lXlPicType = xlPicture If lPicType = 3 Then res = MsgBox("BMP & EMF available" & vbCr & _ "press Yes for BMP, No for EMF", vbYesNoCancel) If res = vbYes Then lXlPicType = xlBitmap ElseIf res = vbNo Then lXlPicType = xlPicture Else Exit Sub End If End If Else MsgBox "No picture on clipboard" |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting a picture onto a userform without using the vba window
On Sun, 29 Nov 2009 08:16:02 -0800, Roger on Excel
wrote: A question I have is how does one make it so that it so that the picture stays on the form when I reopen it? When I close and reopen the form, the picture disappears. I have an image "pop" code snippet that places an image up *after* deleting the current image. The difference being that the image remains. caveat: ONLY if you perform a save of the workbook. This is a modified piece of code I got here, from another author. You would have to decide where to plug it into your code and modify the target cell range in "pop". It will auto-size the images to fit whatever "box" you set up. There are two routines: Sub Pop() On Error Resume Next ActiveSheet.Shapes("Popped").Delete InsertPicture Range("H7").Value, _ Range("H7:I22"), "Popped" End Sub Sub InsertPicture(PictureFileName As String, TargetCells As Range, picName As String) ' inserts a picture and resizes it to fit the TargetCells range Dim p As Object, t As Double, l As Double, w As Double, h As Double If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub If Dir(PictureFileName) = "" Then Exit Sub ' import picture Set p = ActiveSheet.Pictures.Insert(PictureFileName) 'Name the picture so you can delete it later.... p.Name = picName ' determine positions With TargetCells t = .Top l = .Left w = .Offset(0, .Columns.Count).Left - .Left h = .Offset(.Rows.Count, 0).Top - .Top End With ' position picture With p .Top = t .Left = l .Width = w .Height = h End With Set p = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Putting a picture behind text in Excel 2007 | Excel Discussion (Misc queries) | |||
Chart Picture Size in UserForm/Picture Control. | Excel Programming | |||
Duplicated code window and userform window problem | Excel Programming | |||
putting validation to TextBox in the userform | Excel Programming | |||
putting validation to TextBox in the userform | Excel Programming |