Put images in comments
February 13, 2009 • JP • No Comments • Rate This Article
On the ms_excel yahoo group, someone asked if you can put images in comments (on an Excel worksheet, of course). I went ahead and wrote the following VBA code to do so.
This code takes five parameters:
- A string literal representing a single cell range for the comment
- Path and filename of the image you want in the comment box
- Width and
- height of the image
- A boolean variable indicating whether you want the comment to remain visible.
width As Double, height As Double, IsVisible As Boolean) As Boolean
' adds image to comment box
' by Jimmy Pena, http://www.codeforexcelandoutlook.com/, 2/13/2009
' strRange = A string literal representing a single cell range for the comment
' strFilePath = Path and filename of the image you want in the comment box
' width, height = Width and height you want for the image (respectively)
' IsVisible = A boolean variable indicating whether you want the comment to remain visible.
Application.ScreenUpdating = False
' check if file exists
If FileLen(strFilePath) = 0 Then
AddCommentPic = False
GoTo ExitProc
End If
' make sure height and width are positive
If (width < 1) Or (height < 1) Then
AddCommentPic = False
GoTo ExitProc
End If
' check if range is valid
Dim rng As Excel.Range
On Error Resume Next
Set rng = Range(strRange)
If Err <> 0 Then
AddCommentPic = False
GoTo ExitProc
End If
On Error GoTo 0
' scale down
height = height / 100
width = width / 100
' if we made it this far, insert the comment and picture
On Error GoTo ErrorHandle
With rng
.AddComment
With .Comment
.Visible = IsVisible
With .Shape
.Fill.UserPicture strFilePath
.ScaleHeight height, msoFalse
.ScaleWidth width, msoFalse
End With
End With
End With
' if we made it this far, assume success
AddCommentPic = True
GoTo ExitProc
ErrorHandle:
AddCommentPic = False
ExitProc:
Set rng = Nothing
Application.ScreenUpdating = True
End Function
The function itself returns true if it runs successfully (i.e. a comment is added to the worksheet with an image in it) and should be called as follows:
Dim filen As String
Dim success As Boolean
filen = "C:\MyFolder\SomePicture.JPG"
success = AddCommentPic("A1", filen, 160, 600, True)
Debug.Print success
End Sub
The height and width are just estimates, if anyone has a better way of calculating the appropriate height and width, I'd be glad to hear it. The ScaleHeight and ScaleWidth properties only take very small numbers indicating how many times larger the comment image size should be.
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Now you can Twitter from Outlook too
Next Post: Twitter From Word too!
[...] How to put images in a cell comment [...]