Forum Discussion

WLLIAN's avatar
WLLIAN
Brass Contributor
Nov 10, 2024

" imagePath = ThisWorkbook.Path & "\Images\" & selectedName & ".jpg" ' Adjust the path as needed"

I dont think this is it.

The images are in the file, in another tab.
This you are telling to bring the images from a folder, right?

I want to load the image shape to image object on userform, then when i select another name change the image automatic.

thanks dude.

 

 

 

 

 

Alikoc's avatar
Alikoc
MCT
Nov 10, 2024

Hello,

Understand...

Since the images are in another worksheet within the same Excel file, you can use VBA to copy the image from the worksheet into the UserForm’s Image control dynamically based on the selected item in the ListBox.

There are some points you should pay attention to.

Ensure Each Image Has a Unique Name: On the worksheet with the images, each image should have a unique name that corresponds to the names in the ListBox.

Modify the UserForm Code: Use the ListBox1_Change event to load the corresponding image into the Image control on the UserForm.

Can you try the command like this?

 

Private Sub UserForm_Initialize()
    ' Populate the ListBox with names (or IDs) from the worksheet or directly
    With ListBox1
        .AddItem "Name1"
        .AddItem "Name2"
        ' Add as many items as needed, or load dynamically from a range
    End With
End Sub

Private Sub ListBox1_Change()
    Dim selectedName As String
    Dim ws As Worksheet
    Dim imgShape As Shape
    
    ' Set the worksheet where images are stored
    Set ws = ThisWorkbook.Sheets("ImagesSheet") ' Replace "ImagesSheet" with your actual sheet name
    
    ' Get the selected name from the ListBox
    selectedName = ListBox1.Value
    
    ' Try to find the shape with the same name as selected in the ListBox
    On Error Resume Next
    Set imgShape = ws.Shapes(selectedName)
    On Error GoTo 0
    
    ' If the shape exists, load it into the Image control on the UserForm
    If Not imgShape Is Nothing Then
        ' Export the shape as a temporary image
        Dim tempPath As String
        tempPath = Environ("TEMP") & "\" & selectedName & ".jpg"
        imgShape.Copy
        ws.Paste
        ws.Shapes(ws.Shapes.Count).Export tempPath
        ws.Shapes(ws.Shapes.Count).Delete
        
        ' Load the exported image into the Image control
        Image1.Picture = LoadPicture(tempPath)
        
        ' Optionally delete the temporary file if you want
        Kill tempPath
    Else
        MsgBox "Image not found for " & selectedName, vbExclamation
    End If
End Sub

Best Regards,

Ali Koc

  • WLLIAN's avatar
    WLLIAN
    Brass Contributor
    Nov 10, 2024

    I think you did not understand what i'm trying to do.

    If you see the project, when you select a name on the listbox, on the worksheet change the image perfect.

    On that action the name select and the image match. Perfect.

    The final result that i'm tryng to find is, take that image and load to object image on Userform.

    Thanks my friend for the help.

     

    • WLLIAN's avatar
      WLLIAN
      Brass Contributor
      Nov 17, 2024

      Hi, i have a upgrade of the project.

      But, the thing is there is a way to remove the borders in Image Control?

      The picture lost some quality(Frames), there is a way to fix that?

      The keyboard Up and Down stop working.

      Code:

      Private Sub ListBox1_Click()
      ' Verifica se um item está selecionado na ListBox
      If ListBox1.ListIndex >= 0 Then
      ' Atualiza o TextBox1 com o nome correspondente
      TextBox1 = ListBox1.List(ListBox1.ListIndex, 1)

      ' Carrega a imagem associada ao nome selecionado
      Call CarregarImagem
      End If
      End Sub

      Sub CarregarImagem()
      Dim nome As String
      Dim imgShape As Shape
      Dim imgRange As Range
      Dim wsFotos As Worksheet
      Dim tempPath As String

      ' Desativa atualizações visuais para evitar que a tela fique piscando
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual

      On Error GoTo ErrorHandler

      ' Referência à planilha onde estão as imagens
      Set wsFotos = ThisWorkbook.Worksheets("FOTO")

      ' Obtém o nome selecionado na ListBox (coluna 1)
      nome = ListBox1.List(ListBox1.ListIndex, 1)

      ' Verifica se o nome existe na planilha "FOTO"
      Set imgRange = wsFotos.Columns(1).Find(What:=nome, LookIn:=xlValues, LookAt:=xlWhole)

      If Not imgRange Is Nothing Then
      ' Procura a imagem na mesma linha do nome encontrado
      For Each imgShape In wsFotos.Shapes
      If Not Intersect(imgShape.TopLeftCell, imgRange.Offset(0, 1)) Is Nothing Then
      ' Exporta a imagem temporariamente
      tempPath = Environ("Temp") & "\" & nome & ".jpg"
      imgShape.Copy
      With wsFotos.ChartObjects.Add(1, 1, imgShape.Width, imgShape.Height)
      .Activate
      .Chart.Paste
      .Chart.Export tempPath
      .Delete
      End With

      ' Carrega a imagem no controle Image1
      Image1.Picture = LoadPicture(tempPath)
      Exit Sub
      End If
      Next imgShape
      Else
      MsgBox "Imagem não encontrada para o nome: " & nome, vbExclamation, "Erro"
      Image1.Picture = LoadPicture("") ' Limpa a imagem
      End If

      Exit Sub

      ErrorHandler:
      MsgBox "Erro ao carregar a imagem: " & Err.Description, vbCritical, "Erro"
      Image1.Picture = LoadPicture("") ' Limpa a imagem
      Finally:
      ' Restaura as configurações após a execução
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Application.Calculation = xlCalculationAutomatic
      End Sub

      Private Sub UserForm_Initialize()
      ' Limpa a imagem ao inicializar o formulário
      Image1.Picture = LoadPicture("")
      End Sub

Resources