Hello!!!

I wont to make macro who is going to make letters larger in all comments
that I have in may workbook.

And if its possible when I write new comment to have larger letter.

Thanks for any help
best regards
ivica

See:

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


--
Gary''s Student - gsnu2007k


"lopina" wrote:

Hello!!!

I wont to make macro who is going to make letters larger in all comments
that I have in may workbook.

And if its possible when I write new comment to have larger letter.

Thanks for any help
best regards
ivica

Gary''s Student has given you a link for a macro that you can use to change
all your existing Comments to the size you want (see "Format All Comments"
at the link). However, this doesn't address your other question of how to
change newly inserted Comments (obviously, without having to remember to run
the macro each time). There is a minor problem with being able to do this
since inserting a Comment does not trigger any events, so the VBA world has
no way of knowing it happened. I can give you code that *almost* does what
you want... it will not change the Comment's size when it is inserted unless
you change the active cell's location or select another sheet. That means if
you insert a Comment and click back into that same cell, the Comment's size
will not have changed... however, as soon as you click into another cell or
select another sheet, the Comment you just inserted will have its text size
changed.

Here is the event code that does this. To install it, right click the Excel
icon immediately to the left of the File menu item and select View Code.
Doing this will bring up the ThisWorkbook code window... simply copy/paste
the following into that window...

'************ START OF CODE ************
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
HandleCommentSizing
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
HandleCommentSizing
End Sub

Sub HandleCommentSizing()
Static PrevCellAddress As String
Static PrevCellSheetName As String
If Len(PrevCellAddress) 0 Then
With Worksheets(PrevCellSheetName).Range(PrevCellAddres s)
If Not .Comment Is Nothing Then
If .Comment.Shape.TextFrame.Characters.Font.Size < 14 Then
.Comment.Shape.TextFrame.Characters.Font.Size = 14
End If
End If
End With
End If
PrevCellAddress = ActiveCell.Address
PrevCellSheetName = ActiveCell.Parent.Name
End Sub
'************ END OF CODE ************

--
Rick (MVP - Excel)


"lopina" wrote in message
...
Hello!!!

I wont to make macro who is going to make letters larger in all comments
that I have in may workbook.

And if its possible when I write new comment to have larger letter.

Thanks for any help
best regards
ivica

This code will do just fine for me

Thanks for help

regards
lopina




"Rick Rothstein" wrote in message
...
Gary''s Student has given you a link for a macro that you can use to
change all your existing Comments to the size you want (see "Format All
Comments" at the link). However, this doesn't address your other question
of how to change newly inserted Comments (obviously, without having to
remember to run the macro each time). There is a minor problem with being
able to do this since inserting a Comment does not trigger any events, so
the VBA world has no way of knowing it happened. I can give you code that
*almost* does what you want... it will not change the Comment's size when
it is inserted unless you change the active cell's location or select
another sheet. That means if you insert a Comment and click back into that
same cell, the Comment's size will not have changed... however, as soon as
you click into another cell or select another sheet, the Comment you just
inserted will have its text size changed.

Here is the event code that does this. To install it, right click the
Excel icon immediately to the left of the File menu item and select View
Code. Doing this will bring up the ThisWorkbook code window... simply
copy/paste the following into that window...

'************ START OF CODE ************
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
HandleCommentSizing
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
HandleCommentSizing
End Sub

Sub HandleCommentSizing()
Static PrevCellAddress As String
Static PrevCellSheetName As String
If Len(PrevCellAddress) 0 Then
With Worksheets(PrevCellSheetName).Range(PrevCellAddres s)
If Not .Comment Is Nothing Then
If .Comment.Shape.TextFrame.Characters.Font.Size < 14 Then
.Comment.Shape.TextFrame.Characters.Font.Size = 14
End If
End If
End With
End If
PrevCellAddress = ActiveCell.Address
PrevCellSheetName = ActiveCell.Parent.Name
End Sub
'************ END OF CODE ************

--
Rick (MVP - Excel)


"lopina" wrote in message
...
Hello!!!

I wont to make macro who is going to make letters larger in all comments
that I have in may workbook.

And if its possible when I write new comment to have larger letter.

Thanks for any help
best regards
ivica