Add Clipboard Image to UserForm

Hi,

I have seen this posted a few times, but none of the responses I have seen are able to answer my question.
I have a table in a Sheet which contains monitoring data for boreholes, the format of the table is very specific and the color of the cells and text is important for interpretation of the data.

I would like to write a script whereby clicking a button will open a form which then presents the table - as is - within the boundaries of the form.
I am using Excel 2010, so the Spreadsheet control is no longer around, and I can do the process manually by using the picture control and setting the source to "(metafile)" or copying the spreadsheet as a picture and pasting it into the properties of the picture control.

I know there is a workaround in getting the Spreadsheet Control via a download, but if I want to distribute my Workbook the user will need to have this installed too, which is not an option for me.

Is there something I can script to accomplish this?
On top of that, is there a way then to automate the form so that the width and height of the form changes based on the table width and height? The data is dynamic, so I would prefer to not have to re-work the script everytime the monitoring data is returned.

Any thoughts / suggestions are welcomed.

K

Wow thank you.
I guess I wasnt searching in the right areas.

I will try and implement this and let you know if I got it to work.

Thank you!

I tried something similar to the above code, but it crashed for me... Another technique I found was to use the chart object, which worked ok. Try this in a User Form module with a image called 'Image1' on it. (Tried to attach workbook, but no luck...)

Please Login or Register  to view this content.

Thank you all!

I will test these out now.
I am trying to avoid writing to a directory, as I am unsure where this will be run from (if that makes sense)

Using the Clipboard will avoid potential errors if the user has a drive different to the drive I specify as the "bitmap" save location.

I thought I recognised this.
I downloaded the XLS from the OALTD website last week, I have the same error with regards to having to update it to 64 bit.
I will google how to do this, because it is now the second time I have seen it posted as an option to do what I need it to.

Ok...done...here is the final code....thank you all that assisted...xlnitwit your idea is what used.

In a new module:

Please Login or Register  to view this content.

Then in the new form I added this code, with a Picture Control:

Please Login or Register  to view this content.

A summary of changes:

1) Added PtrSafe to all the Declares
2) Changed "olepro32.dll" to "oleautpro.dll"
3) Changed how it selects and copies the table.

Hopefully this will help someone in future.

Thanks again for all the help!

Which line of code does it break at? Perhaps try putting a small delay before that line?

Run-time error '1004'
CopyPicture method of Range class failed

It fails at

Please Login or Register  to view this content.

Thank you.

I'd suggest adding this at the top of your module

Please Login or Register  to view this content.

And then this before the CopyPicture line

Please Login or Register  to view this content.

The sleep time is in milliseconds - adjust if required. I wrote some screen capture code once that stopped working after we upgraded to Windows 7. This fixed it for me - hope it helps you too!

Awesome, thank you.

That seems to have done it.

Hi, I know it's been 4 years since this post, but I can't get it to work on Excel365 - Windows10.
Any help would be appreciated.

Originally Posted by JCabral

Hi, I know it's been 4 years since this post, but I can't get it to work on Excel365 - Windows10.
Any help would be appreciated.

Hi, i also struggeled to get it working on Excel365 and Windows10, both 64bit.

i finally got it working with this ppicture-modul:

Originally Posted by xlnitwit

To work with either you would require something like this- attached as the code seems to be too long to paste inline.

then i use it like this

Please Login or Register  to view this content.

hope this helps