I have set up a macro which inserts a picture onto a cell (AH34), based upon
the value of another cell (L21 - which provides the filename).

Dim s As String
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select


My problem is that if the file does not exist, I get an error box "type
mismatch"

I would like the situation that if a file is not available, it enters "photo
not available" into the cell instead

Something like
IF file=true insert phto
IF file=false inserts "Photo not available"


Any ideas?

Try:

Dim s As Variant
--
CFS


"Stevep4" wrote:

I have set up a macro which inserts a picture onto a cell (AH34), based upon
the value of another cell (L21 - which provides the filename).

Dim s As String
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select


My problem is that if the file does not exist, I get an error box "type
mismatch"

I would like the situation that if a file is not available, it enters "photo
not available" into the cell instead

Something like
IF file=true insert phto
IF file=false inserts "Photo not available"


Any ideas?

CAn you be a little more specific. What else do I need to put into the code
for it to input something like "Corry no photo" into the cell if the photo
file is not available

Dim s As variant
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select


"CFS" wrote:

Try:

Dim s As Variant
--
CFS


"Stevep4" wrote:

I have set up a macro which inserts a picture onto a cell (AH34), based upon
the value of another cell (L21 - which provides the filename).

Dim s As String
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select


My problem is that if the file does not exist, I get an error box "type
mismatch"

I would like the situation that if a file is not available, it enters "photo
not available" into the cell instead

Something like
IF file=true insert phto
IF file=false inserts "Photo not available"


Any ideas?

Sorry, my previous answer is incomplete. Again:

Sub InsertPicture()
Dim s As Variant
s = "C:\pictures\" & Range("L21").Value & ".jpg"
If Dir(s) = "" Then
MsgBox "File does not exist!"
Else
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select
End If
End Sub


--
CFS


"Stevep4" wrote:

I have set up a macro which inserts a picture onto a cell (AH34), based upon
the value of another cell (L21 - which provides the filename).

Dim s As String
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select


My problem is that if the file does not exist, I get an error box "type
mismatch"

I would like the situation that if a file is not available, it enters "photo
not available" into the cell instead

Something like
IF file=true insert phto
IF file=false inserts "Photo not available"


Any ideas?

Can I have that message text into the destination cell itself? for example,
if I am calling for 10 photos in total and only 9 exist, the file would show
9 photos. the 10th cell would display "file does not exist"

"CFS" wrote:

Sorry, my previous answer is incomplete. Again:

Sub InsertPicture()
Dim s As Variant
s = "C:\pictures\" & Range("L21").Value & ".jpg"
If Dir(s) = "" Then
MsgBox "File does not exist!"
Else
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select
End If
End Sub


--
CFS


"Stevep4" wrote:

I have set up a macro which inserts a picture onto a cell (AH34), based upon
the value of another cell (L21 - which provides the filename).

Dim s As String
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select


My problem is that if the file does not exist, I get an error box "type
mismatch"

I would like the situation that if a file is not available, it enters "photo
not available" into the cell instead

Something like
IF file=true insert phto
IF file=false inserts "Photo not available"


Any ideas?

Use

Range("ah34") = "File does not exist!"

instead of

MsgBox "File does not exist!"
--
CFS


"Stevep4" wrote:

Can I have that message text into the destination cell itself? for example,
if I am calling for 10 photos in total and only 9 exist, the file would show
9 photos. the 10th cell would display "file does not exist"

"CFS" wrote:

Sorry, my previous answer is incomplete. Again:

Sub InsertPicture()
Dim s As Variant
s = "C:\pictures\" & Range("L21").Value & ".jpg"
If Dir(s) = "" Then
MsgBox "File does not exist!"
Else
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select
End If
End Sub


--
CFS


"Stevep4" wrote:

I have set up a macro which inserts a picture onto a cell (AH34), based upon
the value of another cell (L21 - which provides the filename).

Dim s As String
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select


My problem is that if the file does not exist, I get an error box "type
mismatch"

I would like the situation that if a file is not available, it enters "photo
not available" into the cell instead

Something like
IF file=true insert phto
IF file=false inserts "Photo not available"


Any ideas?

This does work if I have basic data in the feeder cell such as "HP97" which
would then become HP97.jpg.

Sub InsertPicture()
Dim s As Variant
s = "C:\pictures\" & Range("L21").Value & ".jpg"
If Dir(s) = "" Then
Range("ah34") = "File does not exist!"
Else
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select
End If

It becomes a mess if the information in the feeder cell is #REF. I will
ensure that the information for the feeder cell is either live info or "0".
The "file does not exist" text does not dissapear, but this is not a problem.

Thanks for the help





"CFS" wrote:

Use

Range("ah34") = "File does not exist!"

instead of

MsgBox "File does not exist!"
--
CFS


"Stevep4" wrote:

Can I have that message text into the destination cell itself? for example,
if I am calling for 10 photos in total and only 9 exist, the file would show
9 photos. the 10th cell would display "file does not exist"

"CFS" wrote:

Sorry, my previous answer is incomplete. Again:

Sub InsertPicture()
Dim s As Variant
s = "C:\pictures\" & Range("L21").Value & ".jpg"
If Dir(s) = "" Then
MsgBox "File does not exist!"
Else
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select
End If
End Sub


--
CFS


"Stevep4" wrote:

I have set up a macro which inserts a picture onto a cell (AH34), based upon
the value of another cell (L21 - which provides the filename).

Dim s As String
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select


My problem is that if the file does not exist, I get an error box "type
mismatch"

I would like the situation that if a file is not available, it enters "photo
not available" into the cell instead

Something like
IF file=true insert phto
IF file=false inserts "Photo not available"


Any ideas?