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

**andytesting** · 07-20-2017, 07:17 AM

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'

Private Sub CommandButton1_Click()
   Dim Found As Range
    If Me.ComboBox1.value = "" Then
        MsgBox "Select valid ID. ", , "Missing Entry"
    Else
        Set Found = Sheets("Comments").Range("C:C").Find(What:=Me.ComboBox1.value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
        If Found Is Nothing Then
            MsgBox "Select a valid AIR ID ", "No Match Found"
        Else
           Found.Offset(0, -1).value = Date & ": " & TextBox4.Text & vbCrLf & TextBox2.Text
           Found.Offset(0, 1).value = TextBox4.Text
           ComboBox1_Change
        End If
   
End If
     'Update
End Sub

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

  .TextBox14.value = Format(Sheets("Comments").Cells(.ComboBox1.ListIndex + 3, 4), ";;")
  .Image1.Picture =

Appreciate any help,

Thanks.

**Bernie Deitrick** · 07-20-2017, 11:16 AM

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.

Private Sub CommandButton1_Click()
    Dim rngC
    Dim shtA As Worksheet
    Dim chtTemp As Chart
    Dim strFname As String
    Dim w As Double
    Dim h As Double
    Dim ID As Variant
    Dim rngID As Range
    Dim Found As Range
    Dim v As Variant
    
    Set shtA = Worksheets("Issue Overview")
    Set rngID = shtA.Columns(1)  'Column A with the ID values
    
    ID = Me.ComboBox1.Value
    
    If ID = "" Then
        MsgBox "Select an ID. ", , "Missing Entry"
        Exit Sub
    End If
    
    v = Application.Match(ID, Sheets("Comments").Range("C:C"), False)
    
    If IsError(v) Then
        MsgBox "Select a valid AIR ID.", "No Match Found"
        Exit Sub
    Else
       
        Set rngC = Intersect(shtA.Range("I:Q"), shtA.Rows(Application.Match(ID, rngID, False)))
        
        With rngC
            w = .Cells(1, .Columns.Count).Left - .Cells(1, 1).Left + .Cells(1, .Columns.Count).Width
            h = .Cells(.Rows.Count, 1).Top - .Cells(1, 1).Top + .Cells(.Rows.Count, 1).Height
            .CopyPicture
        End With
        
        shtA.ChartObjects.Add Left:=200, Top:=50, Width:=w, Height:=h
        shtA.ChartObjects(1).Activate
        ActiveChart.Paste
        strFname = ThisWorkbook.Path & "\" & rngC.Address & ".gif"
        ActiveChart.Export Filename:=strFname, filtername:="Gif"
        Me.Image1.Picture = LoadPicture(strFname)
        Me.Image1.Left = (Me.Width - w) / 2
        Me.Image1.Top = 50
        Me.Image1.Width = w
        Me.Image1.Height = h
        shtA.ChartObjects(1).Delete
    
        Set Found = Sheets("Comments").Range("C:C").Cells(v)
        Found.Offset(0, -1).Value = Date & ": " & TextBox4.Text & vbCrLf & TextBox2.Text
        Found.Offset(0, 1).Value = TextBox4.Text
        Found.Offset(0, 2).Value = strFname
        ComboBox1_Change
        
    End If
    'Update
End Sub

**andytesting** · 07-24-2017, 09:44 AM

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:

Me.Image1.Picture = LoadPicture(strFname)

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** · 07-24-2017, 09:55 AM

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** · 07-25-2017, 09:12 AM

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):

Sub Macro1()

    Dim s, rng As Range, rngDest As Range

    Set rng = CurrentSheet.Range("I4:Q4")
'for example my choice led me to row 4 this time
    Set rngDest = SecondSheet.Range("D4")
' for example, when the ID from first sheet row 4 is also found in second sheet row 4
    rng.Copy
    With ActiveSheet.Pictures.Paste(link:=True)
        .Left = rngDest.Left
        .Top = rngDest.Top
    End With

End Sub

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** · 07-25-2017, 10:56 AM

Try it like this:

Private Sub CommandButton1_Click()
    Dim rngC
    Dim shtA As Worksheet
    Dim chtTemp As Chart
    Dim strFname As String
    Dim w As Double
    Dim h As Double
    Dim ID As Variant
    Dim rngID As Range
    Dim Found As Range
    Dim v As Variant
    
    Set shtA = Worksheets("Issue Overview")
    Set rngID = shtA.Columns(1)  'Column A with the ID values
    
    ID = Me.ComboBox1.Value
    
    If ID = "" Then
        MsgBox "Select an ID. ", , "Missing Entry"
        Exit Sub
    End If
    
    v = Application.Match(ID, Sheets("Comments").Range("C:C"), False)
    
    If IsError(v) Then
        MsgBox "Select a valid AIR ID.", "No Match Found"
        Exit Sub
    Else
       
        Set rngC = Intersect(shtA.Range("I:Q"), shtA.Rows(Application.Match(ID, rngID, False)))
        
        rngC.Copy
         
        Set Found = Sheets("Comments").Range("C:C").Cells(v)
        
        With Sheets("Comments").Pictures.Paste(link:=True)
            .Left = Found.Left
            .Top = Found.Top
        End With

        Found.Offset(0, -1).Value = Date & ": " & TextBox4.Text & vbCrLf & TextBox2.Text
        Found.Offset(0, 1).Value = TextBox4.Text
        
        ComboBox1_Change
        
    End If
    'Update
End Sub

**xlnitwit** · 07-26-2017, 05:17 AM

Originally Posted by 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.

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** · 07-26-2017, 04:37 AM

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:

Set Found = Sheets("Comments").Range("D:D").Cells(v)

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

Found.Offset(0, 1).value = TextBox4.Text

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

.Right= Found.Right

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:

strFname = ThisWorkbook.Path & "\" & rngC.Address & ".gif"
        ActiveChart.Export Filename:=strFname, filtername:="Gif"
        Me.Image1.Picture = LoadPicture(strFname)
        Me.Image1.Left = (Me.Width - w) / 2
        Me.Image1.Top = 50
        Me.Image1.Width = w
        Me.Image1.Height = h
        shtA.ChartObjects(1).Delete

After the lines

        With Sheets("Comments").Pictures.Paste(link:=True)
            .Left = Found.Left
            .Top = Found.Top

**Bernie Deitrick** · 07-26-2017, 09:34 AM

Try changing

        With Sheets("Comments").Pictures.Paste(link:=True)
            .Left = Found.Left
            .Top = Found.Top
        End With

to

        With Sheets("Comments").Pictures.Paste(link:=True)
            .Left = Found.Offset(0,1).Left
            .Top = Found.Top
        End With

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

**andytesting** · 07-26-2017, 05:48 AM

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** · 07-26-2017, 05:57 AM

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

Range("whatever").CopyPicture
Me.Image1.Picture = PastePicture

**andytesting** · 07-27-2017, 04:02 AM

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

        Set rngC = Intersect(shtA.Range("I:Q"), shtA.Rows(Application.Match(ID, rngID, False)))
        
        rngC.Copy
         
        Set Found = Sheets("Comments").Range("C:C").Cells(v)
        
        With Sheets("Comments").Pictures.Paste(link:=True)
            .Left = Found.Offset(0, 1).Left
            .Top = Found.Top

Previously, you provided me with

strFname = ThisWorkbook.Path & "\" & rngC.Address & ".gif"
        ActiveChart.Export Filename:=strFname, filtername:="Gif"
        Me.Image1.Picture = LoadPicture(strFname)
        Me.Image1.Left = (Me.Width - w) / 2
        Me.Image1.Top = 50
        Me.Image1.Width = w
        Me.Image1.Height = h

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:

       .Image1.Picture = Found.Picture
       .Image1.Picture = rngC.Copy

or

         Set ImageFound = UserForm2.Image2
        With UserForm2.Image2.Paste(link:=True)
            .Left = Found.Offset(0, 1).Left
            .Top = Found.Top
        
        End With

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Userform: Change Combobox list based on a second ComboBox selection

ComboBox values source being changed based on another combobox selection in a UserForm

Calculating/adding userform textbox values based on combobox selection and display to cell

[SOLVED] Userform - Combobox Cell selection Name Range

Userform Combobox to populate based on selection in another combobox

[SOLVED] Pre populating a userform from excel based on combobox selection

Edit Excel database through UserForm based on combobox Selection

Bookmarks

Bookmarks

Posting Permissions