Skip Blanks Inserting Pictures

[dimples_21188]

Hello everyone
I would truly appreciate some help. The formula below works beautifully until two different reasons:

1. The cell is blank or,
2. There isn't an picture in the file that matches the name in the cell

Could someone help me find a way to ignore these errors and allow the code to continue.

THANK YOU

Sub Worksheet_change(ByVal Target As Range)

Dim animal_pic As Picture
Dim pic_location As String
Dim animal_name As String
ActiveSheet.Pictures.Delete
For i = 7 To 50
animal_name = Worksheets("Strains").Cells(i, 3).Value
pic_location = "C:\Users\dimpl\Desktop\Strain Tiles\" & Worksheets("Strains").Cells(i, 3).Value & ".png"

With Worksheets("Strains").Cells(i, 1)
Set animal_pic = ActiveSheet.Pictures.Insert(pic_location)

animal_pic.Top = .Top
animal_pic.Left = .Left
animal_pic.ShapeRange.LockAspectRatio = msoFalse
animal_pic.Placement = xlMoveAndSize
animal_pic.ShapeRange.Width = 75
animal_pic.ShapeRange.Height = 75


End With
Next i


End Sub

 

[severynm]

Hi!

See this link I like for proper error handling in VBA. However, I think what you have is simple enough we can just handle it with some if statements.

To check if a cell is blank, check if its value equals vbnullstring (which is the same as ""):

    If ThisWorkbook.Worksheets("Strains").Cells(i, 3).Value = vbNullString Then
        '...
    End If

To check if file exists, make use of the Dir function with the file path and evaluate its result. If the result is vbnullstring then the file does not exist:

    If Dir("C:\Users\dimpl\Desktop\Strain Tiles\" & Worksheets("Strains").Cells(i, 3).Value & ".png") = vbNullString Then
        '...
    End If

So, I might combine those two together and only do the picture operations if both results are not blank, i.e.,

If cellValue <> vbNullString And Dir(imagePath) <> vbNullString Then
        'Image insert and manipulations here
    End If

This of course assumes that if there is an error you just want to do nothing and skip to the next loop iteration as you mentioned.

 

[dimples_21188]

Hi!

See this link I like for proper error handling in VBA. However, I think what you have is simple enough we can just handle it with some if statements.

To check if a cell is blank, check if its value equals vbnullstring (which is the same as ""):

    If ThisWorkbook.Worksheets("Strains").Cells(i, 3).Value = vbNullString Then
        '...
    End If

To check if file exists, make use of the Dir function with the file path and evaluate its result. If the result is vbnullstring then the file does not exist:

    If Dir("C:\Users\dimpl\Desktop\Strain Tiles\" & Worksheets("Strains").Cells(i, 3).Value & ".png") = vbNullString Then
        '...
    End If

So, I might combine those two together and only do the picture operations if both results are not blank, i.e.,

If cellValue <> vbNullString And Dir(imagePath) <> vbNullString Then
        'Image insert and manipulations here
    End If

This of course assumes that if there is an error you just want to do nothing and skip to the next loop iteration as you mentioned.

thank you ill give that a go. really thanks, this has been puzzling me for sometime