Excel, how do I set up default comment containing picture?

[Guest]

I've tried right-clicking on the comment & selecting "set auto shape
defaults, to no avail. Is it possible to set up a default comment, containing
a picture, to save having to edit every comment?

 

[Dave Peterson]

Maybe you could just create your default comment once, then
edit|copy
edit|paste special|Comments
whenever you need that special comment.

 

[Guest]

Thanks for the suggestion Dave, that's what I currently do. I suppose I'm
just being lazy wanting the system to do it for me.

 

[Dave Peterson]

You could always put a comment in your personal.xls (sheet1?), then use a macro
to copy|paste that comment.

 

[Guest]

I tried that a while ago & it didn't work. I can't remember what the problem
was, now. I'll give it another go.
thanks very much anyway.

 

[Debra Dalgleish]

Or you could record a macro while you add the comment with a picture,
and run that when you need it. Put the code in a workbook that's always
open, and add a button to one of your toolbars, to run the macro.

Then, select a cell and click the button.

I tried that a while ago & it didn't work. I can't remember what the problem
was, now. I'll give it another go.
thanks very much anyway.

:

 

[Guest]

I've just tried that but got the message, "Run-time error '438'. Object
doesn't support this property or method.

Or you could record a macro while you add the comment with a picture,
and run that when you need it. Put the code in a workbook that's always
open, and add a button to one of your toolbars, to run the macro.

Then, select a cell and click the button.

 

[Debra Dalgleish]

The following code adds a comment with a picture in the c:\Data folder.
You could change that to the path and name of your picture.

'==========================
Sub CommentAddPic()
'adds formatted comment with picture
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment Text:=""
Set cmt = ActiveCell.Comment
With cmt.Shape.TextFrame.Characters.Font
.Bold = False
.ColorIndex = 0
End With
cmt.Shape.Fill.UserPicture "C:\Data\TestPic.jpg"
End If
End Sub
'==========================

 

[Guest]

Debra,
I've copied your formula but when I try to run it, the apostrophe above the
line
"Sub CommentAddPic()" is highlighted & I get the message "Compile error
Expected End Sub".

 

[Dave Peterson]

I think you pasted Debra's code into the middle of another procedure.

Remove that pasted code and add it to the bottom of the module--or put it in a
different module???

 

[Guest]

Debra/Dave,
Between you, you've made an old man very happy.
thank you both.
Peter