Forum Discussion

WLLIAN's avatar
WLLIAN
Brass Contributor
Oct 25, 2024
Solved

EXCEL VBA IMAGE SHAPE ERROR ON SEARCH

HI,

HOW TO FIX THE SEARCH OF THE IMAGE SHAPE ON ERROR?

I HAVE A FOLDER WITH ALL PICTURES, BUT LET'S SAY THAT I DONT HAVE ONE IMAGE OR THE NAME IS WRONG.

I WISH ON THAT CASE TO BRING A FILE "0" FROM THE FOLDER TO THE IMAGE SHAPE.

FILE "0" IS A IMAGE THAT SAYS "NO IMAGE."

 

EXCEL 365

WINDOWS 64

NOTEBOOK

 

HERE IS THE CODE:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
FOTO = "C:\A\FOTO JBA\" & Range("O1").Value & ".JPG"
ActiveSheet.Shapes("Imagem 1").Fill.UserPicture (FOTO)
Exit Sub
ERRORDATO:
ActiveSheet.Shapes("Imagem 1").Fill.UserPicture ("C:\A\FOTO JBA\0.JPG")
Exit Sub
End Sub

 

 

 

excel
Formulas and Functions
Macros and VBA
office 365
  • NikolinoDE's avatar
    NikolinoDE
    Oct 25, 2024

    WLLIAN 

    You can achieve this using the Dir function to verify if the file exists before attempting to load it. Here's an improved version of your code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ERRORDATO
    
    Dim FOTO As String
    FOTO = "C:\A\FOTO JBA\" & Range("O1").Value & ".JPG"
    
    ' Check if the file exists before attempting to load it
    If Dir(FOTO) <> "" Then
        ActiveSheet.Shapes("Imagem 1").Fill.UserPicture (FOTO)
    Else
        ' If the file does not exist, load the default "no image" file
        ActiveSheet.Shapes("Imagem 1").Fill.UserPicture ("C:\A\FOTO JBA\0.JPG")
    End If
    
    Exit Sub

ERRORDATO:
    ' Fallback to the "no image" picture in case of any error
    ActiveSheet.Shapes("Imagem 1").Fill.UserPicture ("C:\A\FOTO JBA\0.JPG")
    Exit Sub
End Sub

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

     

    Happy Excel-ing!

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 25, 2024

    WLLIAN 

    You can achieve this using the Dir function to verify if the file exists before attempting to load it. Here's an improved version of your code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ERRORDATO
    
    Dim FOTO As String
    FOTO = "C:\A\FOTO JBA\" & Range("O1").Value & ".JPG"
    
    ' Check if the file exists before attempting to load it
    If Dir(FOTO) <> "" Then
        ActiveSheet.Shapes("Imagem 1").Fill.UserPicture (FOTO)
    Else
        ' If the file does not exist, load the default "no image" file
        ActiveSheet.Shapes("Imagem 1").Fill.UserPicture ("C:\A\FOTO JBA\0.JPG")
    End If
    
    Exit Sub

ERRORDATO:
    ' Fallback to the "no image" picture in case of any error
    ActiveSheet.Shapes("Imagem 1").Fill.UserPicture ("C:\A\FOTO JBA\0.JPG")
    Exit Sub
End Sub

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

     

    Happy Excel-ing!

Resources