Using cell reference for file path in VBA

[Rob161]

Hi,

I have a macro that inserts a picture into a comment which works fine (see below). However regarding to the file path which is "c:\mypicture.JPG", I would like to instead link this to a cell on my excel document which contains a dynamic file path. How can I do this? Also if the picture is not available, instead of an error message popping up, how can I make my own custom message?

Many thanks in advance!

Sub Add_Comment()
Dim theComment As Comment

Set theComment = Range("A1").Comment
If theComment Is Nothing Then Set theComment = Range("A1").AddComment

With theComment
.Visible = False
.Text Text:="My comment" & Chr(10) & ""
With .Shape
.Fill.Transparency = 0#
.Line.Weight = 0.75
.Line.DashStyle = msoLineSolid
.Line.Style = msoLineSingle
.Line.Transparency = 0#
.Line.Visible = msoTrue
.Line.ForeColor.RGB = RGB(0, 0, 0)
.Line.BackColor.RGB = RGB(255, 255, 255)
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(255, 255, 255)
.Fill.BackColor.SchemeColor = 80
.Fill.UserPicture "c:\mypicture.JPG"
End With
End With

End Sub

 

[Snakehips]

Rob161,

Welcome to MrExcel.

Try....

Sub Add_Comment()
Dim theComment As Comment
Dim MyPath As String


MyPath = Range("D3")  'Edit range that contains path to suit ****


Set theComment = Range("A1").Comment
If theComment Is Nothing Then Set theComment = Range("A1").AddComment


With theComment
    .Visible = False
    .Text Text:="My comment" & Chr(10) & ""
    With .Shape
        .Fill.Transparency = 0#
        .Line.Weight = 0.75
        .Line.DashStyle = msoLineSolid
        .Line.Style = msoLineSingle
        .Line.Transparency = 0#
        .Line.Visible = msoTrue
        .Line.ForeColor.RGB = RGB(0, 0, 0)
        .Line.BackColor.RGB = RGB(255, 255, 255)
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(255, 255, 255)
        .Fill.BackColor.SchemeColor = 80
        On Error Resume Next
        .Fill.UserPicture MyPath
            If Not Err.Number = 0 Then
                MsgBox "You do not have a valid path to the picture file" & Chr(13) & _
                "Please try again.", vbOKOnly, "You Silly-Billy!"
       
               theComment.Delete  'remove the incomplete, pictureless comment ??
            End If
    On Error GoTo 0
    End With
End With


End Sub

Hope that helps.

 

[Rob161]

Thanks Tony, that works like a treat! Just to expand on that, can you please add the extra coding required if I wish to have the macro run automatically when the value of a particular cell changes. I'm new to using VBA and am always getting errors when I attempt this.

Many thanks
Rob