Excel VBA Screenshot Cell range to Userform picture based on combobox selection

[andytesting]

Hi,

I'm new to the forum, please have some patience or correct me if i do something wrong

I'm working on an Excel sheet with a data import on a daily basis. In "Issue Overview" sheet, I import a list daily and add any new issues from the sheet, when compared with a data sheet.
(There are 800+ rows and i have about 73 columns of corresponding information of names, status, etc.)

I've made a userform, in which i want to select 1 ID from the list (combobox1), look it up from the list to display some basic important information. In addition to this, I also copy the list to a "comments" sheet. When a user updates a textbox field, it pushes the comment to this hidden "comments" sheet and the main sheet returns the comment via a lookup, which is also immediately fed back to the userform.

I'm looking for some help to:

• Take a screenshot from columns I -> Q
  This range is for a single row, determined by the selection of the ID from ComboBox1
  Paste the row snapshot to the row corresponding to the ID from the "Comments" sheet
  Show this screenshot in the userform in Image1 when the Id selection is made.
  When the same ID is selected a day or two later, the picture should overwrite the existing one.

This provides me with a "live overview" on the status of the issue
The reason i need a picture is because rows 1 -> Q are conditionally formatted with coloured backgrounds to give a visual aid.

I have some existing code which is working to do this for my comments. It searches the ID and pastes the user input comment using the command 'Found.Offset(0, 1).value = TextBox4.Text'

Please Login or Register  to view this content.

I'm currently missing the link on how i can apply a similar approach to a picture e.g. 'Found.Offset(0, 1).value = Image' then the userform Image 1 looks towards this page and column to reflect the picture

Please Login or Register  to view this content.

Appreciate any help,

Thanks.

[Bernie Deitrick]

Try code like this - you left out a lot of specifics (used ranges for specific information), so you can figure out how to modify it.

Please Login or Register  to view this content.

[andytesting]

Hi Bernie,

Thanks for your response, and apologies for the missing specifics. I adapted your code, only with a minor adjustment to the column in "Issue Overview" sheet (C from A, by adapting the column from 1 to 3) The reason not to list the specifics are that the range is dynamic, other than the column names. The ID is in column C.

I tried your code but get 481 error. Invalid Picture on the following line highlighted:

Please Login or Register  to view this content.

In the folder, the filename is correct, as the range. ($I$5) this corresponds to the ID i selected, which was in row 5. However, it appears not to get the file extension (GIF) I tried to change to PNG but also does not work. (I cannot make any changes to my computer settings due to administration rights. One of the solutions i tried to check for this error).

It looks as though there was a blank box pasted in the Issue Overview sheet in cell F3 at the top. I realised this later after removing the Macro.

Is it also possible to paste the picture in the workbook rather than externally in the folder? In worksheet "Comments" in column D, but the row depends on the selection.

Thanks in advance

[Bernie Deitrick]

Here is a manual method - I will see if I can find VBA code that replicates it:


http://spreadsheetpage.com/index.php...rform_control/

[andytesting]

I did some further research. Apparently, what i'm looking for is not possible : to paste clipboard or recently screenshotted image into userform without first saving it and loading from directory. (this is what, i think you have tried to provide to me Bernie)

I tried to record the Macro: Select a range, copy it and paste as a picture. Next, i selected the userform image box, editted the properties and pressed paste in the Image column. This works, but the macro does not record it.

So for the fixed image I want, this is ok.


However, for the dynamic range snapshot from an unknown row in column I to Q is not yet working.

The following code works to get the correct image in a snapshot (previous code returns a blank object in sheet 1 and stops mid way):

Please Login or Register  to view this content.

How can I apply above to my previous code to take the right snapshot, based on the row determined by Combobox Selection?

How can i then save this temporarily in the workbook path? Give the image a fixed name?

For example, i would like to keep a record of all of the snapshots, to match the row of the Id that is found. (like a historical pasting in the sheet "Comments", matching the row corresponding to the ID that was selected)
However, for the userform image, I only need the current picture from the clipboard to be saved (overwriting) a single temp file to be referenced to the image1 on the userform.

Is anyone able to understand my description and provide some help to achieve it?

Thanks a lot.

[Bernie Deitrick]

Try it like this:

Please Login or Register  to view this content.

[andytesting]

Hi Bernie,

Thanks again for your time ! The code is now working to display the picture correctly and in the correct sheet. However, the placement of the image is not correct.

The image pastes in "Comments", column C, on top of the ID. While this is still fine, i would ideally like it to paste in column D, (offset +1)
I found that i can do this myself by editing the code to be:

Please Login or Register  to view this content.

However, i create a new problem for myself in that the ID in column C (used as the lookup) is overwritten by the comment i typed before i pressed 'Update', which breaks my other code to check this cell value as a reference.
I also disabled the line

Please Login or Register  to view this content.

thinking that was where the information was coming form.

Can you help to edit such that the picture will paste in column D, (an offset of +1 with the current found lookup ID)?
I tried something like

Please Login or Register  to view this content.

but did not work. Sometimes pastes to K, sometimes to E then bugs out.

Additionally, I would like the most recently pasted item to be saved in the workbook path as a temp file. I could not edit the previous code for this to get the right selection.

As an example, i tried to use:

Please Login or Register  to view this content.

After the lines

Please Login or Register  to view this content.

[xlnitwit]

I did some further research. Apparently, what i'm looking for is not possible : to paste clipboard or recently screenshotted image into userform without first saving it and loading from directory.

Hi,

That is not correct. Search for Stephen Bullen's PastePicture code which will allow you to assign a picture from the clipboard directly to an Image control.

[andytesting]

Hi xlnitwit,

I had looked at this several times but as yet failed to adapt it correctly for my sheet, i'll try again later when i get some time to do so. I have seen others reporting it works great.

When i cna paste from clipboard, I won't need to save the image.

[xlnitwit]

Once you have the module added to your workbook, it should be a simple case of something like

Please Login or Register  to view this content.

[Bernie Deitrick]

Try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Change the (0,1) to (0,2) etc. to move the picture further to the right.

[andytesting]

Thanks again Bernie, perfect. (I had the idea of what but not how)

Please Login or Register  to view this content.

Previously, you provided me with

Please Login or Register  to view this content.

It worked well to create a file, but then the file was 0kb (no data and incorrect extension)

Can you help to combine these such that that last entry which was just pasted into "Comments" is saved as "temp.jpg" in the workbook's folder path?

For example, i try to think like:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Obviously i've not got it correct, but am i on the right way of thinking of how to either paste the image directly to userform, or to save it as temp.jpg and hard link the image there and by overwriting the saved file, the userform should also update?

Thanks again