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"