Hi,
I've recently created a spreadsheet with the aim of recording errors in a product.
The easiest way to show these errors is sometimes with a picture and here is the problem. When I use autofilter the pictures do not get filtered. I would therefore like to add the pictures as a comment instead however do not want to go through the long process of clicking all the boxes each time. Idealy I don't want to collect the image from a separate file but would like to paste it directly into a comment.
I have tried various ways of recording Macro's but have had no success.
I have also found the below code after various searches but I don't have a great enough understanding to minipulate it.
Can any one help me?
Thank you,
Stephen
I've recently created a spreadsheet with the aim of recording errors in a product.
The easiest way to show these errors is sometimes with a picture and here is the problem. When I use autofilter the pictures do not get filtered. I would therefore like to add the pictures as a comment instead however do not want to go through the long process of clicking all the boxes each time. Idealy I don't want to collect the image from a separate file but would like to paste it directly into a comment.
I have tried various ways of recording Macro's but have had no success.
I have also found the below code after various searches but I don't have a great enough understanding to minipulate it.
Can any one help me?
Thank you,
Stephen
Code:
Sub PictureIntoComment()
[COLOR=#006600]'www.contextures.com\xlcomments03.html[/COLOR]
Dim ch As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim ws As Worksheet
Dim sName As String
Dim cmt As Comment
Dim sPath As String
Dim sFile As String
Dim rng As Range
Set ws = ActiveSheet
Set rng = ActiveCell
sPath = ThisWorkbook.Path & "\"
sName = InputBox("Name for picture file (no extension)", "File Name")
If sName = "" Then sName = "Picture_" & Format(Date, "yyyymmdd")
sFile = sPath & sName & ".gif"
dWidth = Selection.Width
dHeight = Selection.Height
Selection.Cut
Set ch = ws.ChartObjects.Add(Left:=rng.Left, Top:=rng.Top, _
Width:=dWidth, Height:=dHeight)
ch.Chart.Paste
rng.Activate
ch.Chart.Export sFile
ch.Delete
Set cmt = rng.AddComment
cmt.Text Text:=""
With cmt.Shape
.Fill.UserPicture sFile
.Width = dWidth
.Height = dHeight
End With
End Sub
)
