Excel worksheet cell comment box

[Donald Stockton]

ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:="This is a Test"


The above code will add a comment to the active cell, but how can I also
change the Font properties rather than excel using the default setting?


D.S.

 

[Tom Ogilvy]

Sub E()
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:="This is a Test"
With ActiveCell.Comment.Shape.TextFrame.Characters.Font
.Name = "Times New Roman"
.Size = 12
.Bold = False
End With

End Sub

 

[paul.robinson]

Hi
Search this Group using
"How do I change the font inside a cell comment"

regards
Paul

 

[John]

Hi Donald,

Have a go with this.

Best regards

John



Sub DonaldsComment()

Dim oCmt As Comment

'Set reference to new comment
Set oCmt = ActiveCell.AddComment

'Set visibility & text
oCmt.Visible = False
oCmt.Text "This is a test"

'Set font
With oCmt.Shape.TextFrame.Characters.Font
.Name = "Arial"
.Size = 10
.Bold = False
End With

'Set comment size
With oCmt.Shape
.Width = 100
.Height = 100
End With

End Sub

 

[D.S.]

Thanks to all

Hi Donald,

Have a go with this.

Best regards

John



Sub DonaldsComment()

Dim oCmt As Comment

'Set reference to new comment
Set oCmt = ActiveCell.AddComment

'Set visibility & text
oCmt.Visible = False
oCmt.Text "This is a test"

'Set font
With oCmt.Shape.TextFrame.Characters.Font
.Name = "Arial"
.Size = 10
.Bold = False
End With

'Set comment size
With oCmt.Shape
.Width = 100
.Height = 100
End With

End Sub

 

[D.S.]

One more question about the comment box....

Can it be sized? for instance, can I size it to fit the contents?

 

[paul.robinson]

Hi
Try searching this group using "can I size a comment box to fit the
contents"

regards
Paul

 

[Norman Jones]

Hi Don,

In addition to Paul's advice see Debra Dalgleish's suggestions at:

http://www.contextures.com/xlcomments03.html#Resize

 

[ph8]

With Range("A1")
..addComment ("This is my sized comment")
..Comment.Shape.Height = 11
..Comment.Shape.Width = 123
End With

 

[D.S.]

Thanks to all.. All of your suggestions have been very helpfull.