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(PrevCellAddress)
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" <(E-Mail Removed)> wrote in message
news:gjm4v9$905$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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(PrevCellAddress)
> 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" <(E-Mail Removed)> wrote in message
> news:gjm4v9$905$(E-Mail Removed)...
>> 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
>>
>