Cut and paste objects into userforms

[Roger on Excel]

Is there a way to cut and paste pictures/objects into a userform field such
that a user can paste in an external object (for example a chemical structure
or a screen capture) into the form.

The pasted item in the userform would then need to be be appended to the
spreadsheet at a chosen cell location and also pulled back to the userform
when the user edits the userform (for example changes the embedded picture).

I know how to do this with text (Contextures website) but would like to
learn how to do it with other objects.

Can anyone help?

Regards,

Roger

 

[Peter T]

If you mean copy drawing objects onto a form, see Andy Pope's demo

http://andypope.info/vba/userformdraw.htm

Or for images you can load from file or other ways to an Image control

Regards,
Peter T

 

[Roger on Excel]

Hi Peter,

Many thanks for posting your suggestion

I just read my post again and I dont think I described my problem very well.

I am not needing to be able to create a picture on the form, but find a
suitable control which allows a user of the form in the spreadsheet to paste
an image (or an image upload) which can be stored in the sheet and used in
the sheet.

For example I use the form for entering chemical data which is appended to a
list in the sheet. This is fine, but I would like to paste in a chemical
structure from a ChemDraw drawing package into a form when i enter data.

I am guessing there must be an upload tool for a file such as a jpg or bit
map, but I really prefer to be able to paste in the individual image for each
data entry.

I hope this explains better my requirements.

Regards,

Roger

 

[Peter T]

Afraid I don't follow. You say "upload"; do you mean get/send an image from
a web site, or do you mean get an image from file and display on a sheet
(use Insert Picture method), or do you mean copied as an image from some
other app (manually or programmatically) and copied onto the sheet, or...?

Regards,
Peter

 

[Roger on Excel]

Hi Peter,

I am making a spreadsheet which is to be driven by userforms as I do not
want users to paste pictures into the sheets themselves, rather they would
paste them into a suitable box on a userform and then the user form would
enter the pasted image into the correct cell

To explain better, I use ChemDraw - a chemical drawing package - one usually
copies the structures in ChemDraw and you can paste them directly into Excel
- they appear as a floating selection in excel which can be dragged around,
resized etc.

I would like to paste the copied structure into a suitable dialog box on
userform which then deposits the picture into the correct cell of the
spreadsheet.

 

[Peter T]

I'm still not following

" they appear as a floating selection in excel which can be dragged around"

What is that "floating selection", some sort of object no doubt but what, a
Picture? How did it get in the clipboard (from your code or by user
mouse/keyboard copy), is it an image in the clipboard. Do you want the image
to go straight from clipboard onto the userform (and what do you mean by
into a dialog), or from the floating whatever it is on the sheet then into
the something (dialog?) on the userform.

Regards,
Peter T

 

[Roger on Excel]

Hi Peter,

Thankyou for persisting with me

At present, the chemical structure is created in a separate software
application (ChemDraw) and then copied to the clipboard in ChemDraw. It is
then pasted into excel and appears in excel as a rectangular selection. When
pasted into excel, the picture can be selected and moved around.

The picture resides above a list of chemical information relating to the
chemdraw picture. At present the information is added to the sheet with a
userform (for example - its molecular weight, its density etc)

Each sheet has its own chemical drawing picture (which is different) and it
often needs to be edited or changed.

This is fine, however I dont want the user to paste the picture directly
into the sheet. Rather I would like them to paste the picture (and all the
other data) into a user form which then enters the information (and the
picture) into the spreadsheet.

So - in a nutshell, I want to use a userform to facilitate the pasting of a
picture into excel.

What do you think?

Roger

 

[Peter T]

OK so it's a Picture object in Excel

So - in a nutshell, I want to use a userform to facilitate the pasting of
a
picture into excel.

Is it (the picture) already on the clipboard or does it need to be copied
from the Shape (ie the picture object in Excel); or from the other
application (which unless it is exposed to automation would require SendKeys
to do the copy, and you'll have to figure that on your own).

Regards,
Peter T

 

[Tim Williams]

If the structure needs to be edited, it's easiest to paste in the structure
as an OLE object and not a picture. That way the user can just double-click
on the structure to bring it back into ChemDraw for editing. If it's a
picture then they'll have to redraw the whole thing in order to make the
required change (introducing the chance of more errors).

Since there's only one structure per sheet, it doesn't seem there's much
chance of mixing up structures, so you could just format an area on the
sheet so it's apparent where the structure should be positioned after
pasting.

Tim