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
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