Format a Comment created by a Macro

[Guest]

Thanks to Ken Johnson's help I have a Comment box which appears only if a
condition is met. This comment has been created by a small macro. It seems
that because the comment box is not a standard Excel comment, but has been
created by the macro, any attempt to format the text box (dimensions,
alignment, no shadow etc) fails. It looks like the formatting has to be
included in the macro, but so far I haven't been able to do this. Can anyone
help?

 

[mudraker]

Ken

can you post the code that creates the Comment box

 

[hanjohn]

mudraker,
If you are addressing Ken Johnson here is the code I posted for Ken.G.
If you're addressing Ken.G then please ignore this reply.

Just as a demo, if the value in cell A1 of the active sheet changes to
become greater than 10 then Cell B1 has the comment "A1 is greater than

10" added.
The code must be pasted into the ThisWorkbook module. If you don't
want all sheets affected then paste the code into the WorkSheet_Change

Sub of the sheet you want affected.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If ActiveSheet.Cells(1, 1).Value > 10 Then
With ActiveSheet.Cells(1, 2)
On Error Resume Next 'if no comment to delete then error is
ignored
.Comment.Delete 'remove old comment
On Error GoTo 0
.AddComment
.Comment.Visible = True 'change to False if you only want to
see the comment
'when the cursor is positioned over the

commented cell
.Comment.Text Text:="A1 is greater than 10"
.Comment.Shape.TextFrame.AutoS­ize = True 'comment frame size
will suit any size comment
End With
Else: On Error Resume Next
ActiveSheet.Cells(1, 2).Comment.Delete 'delete old comment because A1
is not >10
On Error GoTo 0
End If
End Sub

Ken Johnson

 

[Guest]

Don't ignore Ken Johnson's post. That's the code I used to create the
conditional comment. I've since tried recording a macro while creating and
formatting a comment, then copied that code into my macro but it keeps coming
up with errors. I don't know enough about vbasic I'm afraid.