Change Image When I Select Name In Listbox

[Wllian_Santos]

Hi, there a way to change the image on userform when i select another name in listbox?
I have a shape that change on the worksheet when i select a name in listbox, but on Userform only change when i initialize.

Here the code on the project:

>>> use code - tags <<<

Private Sub UserForm_Initialize()
Dim cObj As ChartObject, iPath
Application.ScreenUpdating = False
With ActiveSheet.Shapes("Imagem 1")
  Set cObj = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width, .Height)
  .Copy: cObj.Select: cObj.Chart.Paste
  iPath = Environ("Temp") & "\" & Format(Now, "hhmmss") & ".jpg"
  cObj.Chart.Export iPath
End With
Image1.Picture = LoadPicture(iPath)
cObj.Delete
End Sub

Excel 365, Windows, System 64, Notebook

 

[Marc L]

Hi, just use the UserForm event LISTBOX1_CLICK to update the picture via LoadPicture statement …​

​

 

[Wllian_Santos]

Hi, just use the UserForm event LISTBOX1_CLICK to update the picture via LoadPicture statement …​

​

I did not understand.

 

[Marc L]

Could be like this :​

Private Sub LISTBOX1_CLICK()
    TextBox1 = ListBox1.List(ListBox1.ListIndex, 1)
    F$ = ThisWorkbook.Path & "\FOTO\" & TextBox1 & ".jpg"
    If Dir(F) > "" Then Image1.Picture = LoadPicture(F) Else Image1.Picture = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Wllian_Santos]

Perfect.
Thank you my friend

 

[Wllian_Santos]

Could be like this :​

Private Sub LISTBOX1_CLICK()
    TextBox1 = ListBox1.List(ListBox1.ListIndex, 1)
    F$ = ThisWorkbook.Path & "\FOTO\" & TextBox1 & ".jpg"
    If Dir(F) > "" Then Image1.Picture = LoadPicture(F) Else Image1.Picture = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

It's working fine.
But, There is a way to not load or do anything when i dont have the image?

 

[Marc L]

There is a way to not load or do anything when i dont have the image?

That's actually what the code does …​

​

 

[Wllian_Santos]

That's actually what the code does …​

​

Forgive me, you were right. I did not copy the code complete just like you write. This question have been finish. Thank you. You are a God on Excel. Agan, sorry for my mistake.. Thank you

 

[Wllian_Santos]

its not working.
The ideia is get the image from the worksheet.
Your code is to get the image from a folder.
"F$ = ThisWorkbook.Path & "\FOTO\" & TextBox1 & ".jpg""

Lets say you send your file to someone, but the person dont have the image folder, so will no show the images.
With the images on the worksheet, the person who open can used with no problem.

 

[Marc L]

In this case - which was not mentioned in the initial post ! - just allocate the picture​

with the same method used in your original VBA event UserForm_Initialize …​

​

 

[Wllian_Santos]

In this case - which was not mentioned in the initial post ! - just allocate the picture​

with the same method used in your original VBA event UserForm_Initialize …​

​

i did not understand

 

[Marc L]

As the easy only way is to copy the pictures within the workbook folder, whatever manually or​

exporting them from the worksheet like your original VBA event procedure UserForm_Initialize does …​

​

 

[Wllian_Santos]

As the easy only way is to copy the pictures within the workbook folder, whatever manually or​

exporting them from the worksheet like your original VBA event procedure UserForm_Initialize does …​

​

But that code Initialize only works when i open the list box at the first time.
When i select another name in listbox dont show any image.
But thanks my friend for your helpe and time.

 

[Marc L]

You can include a loop in order to export all the pictures if necessary and move the code to​

the workbook VBA event Workbook_Open then delete the useless procedure UserForm_Initialize …​

​

 

[Wllian_Santos]

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:

>>> You've already noted <<<
>>> use code - tags <<<​

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

 

[Marc L]

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

On VBE side via the BorderStyle in the image Properties window (F4) in the Userform …​

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

The easy direct way is to load directly the picture from the original on disk as Excel was designed for …​

​

Or at least the original picture must be imported in Excel with the worksheet at 100% zoom​

with the cell as large as the original picture on disk which should be also as large as the picture object in the UserForm.​

​

When the picture is not well imported within the worksheet like in your attachment​

so in order to lose less quality - but still lose some, will be less than loading directly from the original on disk -​

before to export the temp chart image on disk is to resize the chart object at the size of the Userform image at least …​

​

The keyboard Up and Down stop working.

With the bad idea to store the photos within a worksheet, so according to my post #14​

the UserForm code must contain only the LISTBOX1_CLICK VBA event procedure like in my post #4​

with maybe one more codeline in case the worksheet does not contain a member photo and no more else !​

So 4 codelines only, UserForm_Initialize useless, not necessary …​

​

When the photos are stored within the worksheet so according to my post #14 the idea is at the workbook opening only​

to create the FOTO folder within the workbook folder if necessary and​

to export worksheet photos according to the member name if necessary via the workbook event procedure Workbook_Open​

(into ThisWorkbook module EstaPastaDeTrabalho) so far not within the Userform code module …​

​

 

[Wllian_Santos]

"With the bad idea to store the photos within a worksheet"?
Not everyone understand excel like you.
Let say you send a file to a client, but he dont know Excel VBA Master Book like you.
But only the basics, click on things.
If you try to explained , "Sr can you unzip the picture folder?" "Did you put the file and the folder at the same place?"
Just for you know... you did know how to do my project move on.
Another person did, more smart and kind.
But on Zoom you say, you were right, i try here, doind that the border stay small too. So Thank you.
I will try on another Forum help with Keyboard subject. I dont want talk with a rude person.
But thank you dude for your time. God Bless.

 

[Marc L]

I dont want talk with a rude person.

I'm not rude on you, I have just explained how Excel is designed for,​

so obviously using Excel as not designed for leads to some issues …​

And as it is easy to create a zip file with all the necessary like the FOTO subfolder !​

No issue with the keyboard just following posts #4, 14 & 16 so at level to any good enough reader …​

​