none
How to insert a photo in a userform RRS feed

  • Question

  • Hi

    I need help with inserting a photo in a form. The photo is pasted in a cell in a worksheet: How do I show it in a form via VBA (Excel 2013)?

    If I copy the photo to the clipboard I can't find a control that responds with the paste command…


    :) Søren

    Thursday, September 28, 2017 3:34 PM

Answers

    • Marked as answer by Grundtvig Monday, October 2, 2017 8:39 AM
    Friday, September 29, 2017 5:24 AM
  • Hi Grundtvig,

    I would suggest you export the picture to a temporary file and then load it into the form and delete the temporary file at last. 

    Is the picture full in the range of the cell? In my code, I used TopLeftCell to confirm the shape(picture) is in the cell.

    Here is the example.

    Private Sub UserForm_Initialize()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim shp As Shape
    Dim cht As ChartObject
    temPpath = "C:\Users\v-guaxu\Desktop\image.jpg"
    Set ws = ActiveSheet
    
    For Each shp In ActiveSheet.Shapes
    If Not Intersect(shp.TopLeftCell, ActiveSheet.Range("E14")) Is Nothing Then
    Exit For
    End If
    Next shp
    
    Set cht = ws.ChartObjects.Add(0, 0, shp.Width, shp.Height)
    shp.Copy
    cht.Activate
    ActiveChart.Paste
    ActiveChart.Export temPpath, FilterName:="JPEG"
    cht.Delete
    Image1.Picture = LoadPicture(temPpath)
    Kill temPpath
    Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 29, 2017 9:18 AM

All replies

  • Hi

    I need help with inserting a photo in a form. The photo is pasted in a cell in a worksheet: How do I show it in a form via VBA (Excel 2013)?

    If I copy the photo to the clipboard I can't find a control that responds with the paste command…

    :) Søren


    :) Søren

    Thursday, September 28, 2017 8:59 AM
  • Are you referring to an Access form, or to an Excel Userform?

    -- Scott McDaniel, Microsoft Access MVP

    Thursday, September 28, 2017 10:12 AM
  • Excel Userform

    :) Søren


    • Edited by Grundtvig Thursday, September 28, 2017 11:36 AM
    Thursday, September 28, 2017 11:35 AM
  • If it's pasted as a Shape and not an OLE Object then you will need to use the Windows API:

    http://www.ozgrid.com/forum/showthread.php?t=195522&page=2

    If it's an OLE Object then it's a bit easier:

    Image1.Picture = Worksheets("Sheet2").OLEObjects("Image1").Object.Picture


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, September 28, 2017 12:53 PM
  • This forum is for discussing MS Access database related issues.  I'd urge you to repost/move your question to a more appropriate forum, one relating directly to the software your problem relates to (Excel), to get the qualified help you seek.

    Or is your question how to copy an image from an Excel worksheet to Access?  If so, copy it where exactly?  Normally, you be best to save the image to the HD and simply save the path in Access and display it as required.  If this is what you are after, please give us more details to be in a better position to properly advise you.

    Good luck.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Thursday, September 28, 2017 1:23 PM
    • Marked as answer by Grundtvig Monday, October 2, 2017 8:39 AM
    Friday, September 29, 2017 5:24 AM
  • How do I copy the picture in the cell as a OLEObject?

    I tried this with no success:

    Worksheets(arknr).Cells("H" & linienr).Copy Destination:=UserForm2.Image1.Picture


    :) Søren

    Friday, September 29, 2017 7:29 AM
  • Hi Grundtvig,

    I would suggest you export the picture to a temporary file and then load it into the form and delete the temporary file at last. 

    Is the picture full in the range of the cell? In my code, I used TopLeftCell to confirm the shape(picture) is in the cell.

    Here is the example.

    Private Sub UserForm_Initialize()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim shp As Shape
    Dim cht As ChartObject
    temPpath = "C:\Users\v-guaxu\Desktop\image.jpg"
    Set ws = ActiveSheet
    
    For Each shp In ActiveSheet.Shapes
    If Not Intersect(shp.TopLeftCell, ActiveSheet.Range("E14")) Is Nothing Then
    Exit For
    End If
    Next shp
    
    Set cht = ws.ChartObjects.Add(0, 0, shp.Width, shp.Height)
    shp.Copy
    cht.Activate
    ActiveChart.Paste
    ActiveChart.Export temPpath, FilterName:="JPEG"
    cht.Delete
    Image1.Picture = LoadPicture(temPpath)
    Kill temPpath
    Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 29, 2017 9:18 AM
  • I did it Your way and it was succesful – thanks a lot…

    :) Søren

    Monday, October 2, 2017 8:37 AM
  • Hej Dan

    jeg prøvede om jeg kunne efterligne dine kodelinier, men måtte ty til at hente billedet via harddisken…

    Tak for rådene…


    :) Søren


    • Edited by Grundtvig Monday, October 2, 2017 8:40 AM
    Monday, October 2, 2017 8:39 AM