Image Control

[melcoats]

Hello,

I have images "Pic_00001 - Pic_00005" (A2:E2) in a worksheet "Parts" and I want to pull them into an image box "ImageSKU" using a combo box "cboSKU" in my userform "frmQC".

Any help out there?

Thanks!

 

[Domenic]

Unfortunately, a picture located on a worksheet cannot directly be assigned to an image control. The code would first have to export the image to your local drive, and then it could be assigned to your image control. So, for example, the following code exports the selected picture to your temporary folder, assigns the picture to your image control, and then deletes the exported image from your temporary folder.

Option Explicit

Private Sub cboSKU_Change()

    Dim pic As Picture
    Dim imageFileName As String
    Dim saveAsFileName As String
    Dim errMsg As String
   
    If Me.cboSKU.ListIndex = -1 Then
        Me.ImageSKU.Picture = LoadPicture("")
        Exit Sub
    End If
   
    imageFileName = Me.cboSKU.Value
   
    On Error Resume Next
    Set pic = ThisWorkbook.Worksheets("Parts").Pictures(imageFileName) 'change the sheet name accordingly
    If pic Is Nothing Then
        MsgBox "'" & imageFileName & "' does not exist!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
   
    saveAsFileName = Environ("temp") & "\temp.jpg"

    If Not ExportShapeToImage(pic, saveAsFileName, errMsg) Then
        MsgBox errMsg, vbCritical, "Error"
        Exit Sub
    End If
   
    Me.ImageSKU.Picture = LoadPicture(saveAsFileName)
   
    Kill saveAsFileName
   
End Sub

Function ExportShapeToImage(ByVal shapeToExport As Object, ByVal saveAsFileName As String, ByRef errorMessage As String) As Boolean

    On Error GoTo errorHandler
   
    shapeToExport.CopyPicture appearance:=xlScreen, Format:=xlBitmap
   
    Dim tempWorksheet As Worksheet
    Set tempWorksheet = ThisWorkbook.Worksheets.Add
   
    With tempWorksheet.ChartObjects.Add(Left:=0, Top:=0, Width:=shapeToExport.Width, Height:=shapeToExport.Height)
        .Activate
        With .Chart
            .ChartArea.Format.Line.Visible = msoFalse
            .Paste
            .Export fileName:=saveAsFileName
        End With
        '.Delete
    End With
   
    Application.DisplayAlerts = False
    tempWorksheet.Delete
    Application.DisplayAlerts = True
   
    ExportShapeToImage = True
   
    Exit Function
   
errorHandler:
    errorMessage = "Error " & Err.Number & ":" & vbCrLf & vbCrLf & Err.Description
    ExportShapeToImage = False
   
End Function

Note that during the image export process, a temporary worksheet is added. As a result, your screen will flicker/update. So I would suggest that you set ScreenUpdating to False before showing your form, and then setting back to True once its unloaded...

Sub ShowForm()

    Application.ScreenUpdating = False

    UserForm1.Show
    
    Application.ScreenUpdating = True
    
End Sub

Hope this helps!