please save the cat from further harm.......

[sykes]

Hi chaps
As part of a macro, I'm inserting comments into cells.
The comments are sometimes 150(ish) characters long, and on subsequent inspection of the cells, the comment boxes are not big enough to display all of the text, so I'm having to right click on the cell, select edit comment, then manually make the comment box bigger, which is annoying the hell out of me - I've already throttled the cat 4 times today!
I've tried recording a macro whilst doing the manual operation, and it comes up with the shaperange thing, but I can't seem to get it to run in standalone code again.
I've searched the object browser, done a search of this site, and STILL can't solve the problem.

Part 2 of my post is :

Is there any way to change the default comment box, i.e. from the tahoma font etc.? I know one can change the default background fill colour by going onto the display settings, but seemingly, nothing else.

Please help.....if only for my cat's sake!

Thanks, in anticipation

Sykes

PS To all animal lovers......before you start chastising the MVP's etc etc, ..I havn't got a cat really!


_________________
....and the meek shall inherit the earth...
(but not the mineral rights!)
This message was edited by sykes on 2002-08-24 09:16

 

[Jack in the UK]

hi sykes

Im not gonna answer Q1 as i recon the cat sould get it! i hate cats, none round these parts! even the dogs population is going down fast! I had dog and all.

Q2 default id horrid square, i have documented this many times i have fluffy clouds white and blue! search will help but just in case

make you comment as normal, select drawing toolbar, and select the comment right click to do so, clck the arrow on draw bit of drawing tool bar, click change autoshape and choose away.

fONT editing is:
right click the comment will hash up all you need is there.

Back to Q1, yes this can be done but autofit, cant recall now, ill have a go

HTH

Really.. i do hate cats dogs [most people rally] but i truly wish no one or animals any harm.

 

[Jack in the UK]

Hi Sykes

Its ome to me right click format comment

see alignment thats a feature to autosize..now i noticed you want VBA, ill have a go... at that, be careful as i have a memory that the wrapping is on carrage return, so you might get one very wide comment in just one line

 

[sykes]

Thanks Jak
Unfortunately I didn't mean change the default comment box from it's original font each time, I meant change the default comment font permanently, so that whenever I add a comment, it comes up in, for the sake of argument, Arial, size 12.

Also, I know about the autofit etc etc, but want to do it all with VBA.

Thanks anyway

Sykes

 

[Ivan F Moala]

On 2002-08-24 09:13, sykes wrote:
Hi chaps
As part of a macro, I'm inserting comments into cells.
The comments are sometimes 150(ish) characters long, and on subsequent inspection of the cells, the comment boxes are not big enough to display all of the text, so I'm having to right click on the cell, select edit comment, then manually make the comment box bigger, which is annoying the hell out of me - I've already throttled the cat 4 times today!
I've tried recording a macro whilst doing the manual operation, and it comes up with the shaperange thing, but I can't seem to get it to run in standalone code again.
I've searched the object browser, done a search of this site, and STILL can't solve the problem.

Sykes, this may help you out?<pre/>
Option Explicit

'// if you want to resize ALL comment boxs
'// in an activesheet OR All sheets then this routine
'// may help you. Resizing is @ 25% change as required.
'// Or scale down.

Sub ChangeSize_Comments_SSh()
Dim cCell As Range
Dim sComment As Comment
Dim allComments As Range
Dim Sh As Worksheet
Dim Ans As Integer
Dim All As Boolean

Ans = MsgBox("Activesheet (Yes) or ALL sheets (No)", vbYesNoCancel)
If Ans = 2 Then Exit Sub

All = IIf(Ans = 7, True, False)

If Not All Then Set Sh = ActiveSheet: GoTo skipSh

For Each Sh In ActiveWorkbook.Sheets
skipSh:
On Error Resume Next
Set allComments = Sh.Range("A1").SpecialCells(xlCellTypeComments)
If allComments Is Nothing And Not All Then MsgBox "No comments in " & ActiveSheet.Name: GoTo Ex
'// Leave On Error Resume next ON to take care of Loop not set error
'// when doing active sheet only.

For Each cCell In allComments
With cCell.Comment
'// lock aspect to get even increase in size
.Shape.LockAspectRatio = True
'// increase by 25% change as required
.Shape.Height = .Shape.Height * 1.25
End With
Next cCell
Next Sh

Ex:
Set allComments = Nothing
Set Sh = Nothing

End Sub

Sub ChangeFonts_Comments_SSh()
Dim cCell As Range
Dim sComment As Comment
Dim allComments As Range
Dim Sh As Worksheet
Dim Ans As Integer
Dim All As Boolean

Ans = MsgBox("Activesheet (Yes) or ALL sheets (No)", vbYesNoCancel)
If Ans = 2 Then Exit Sub

All = IIf(Ans = 7, True, False)

If Not All Then Set Sh = ActiveSheet: GoTo skipSh

Application.ScreenUpdating = False
For Each Sh In ActiveWorkbook.Sheets
skipSh:
On Error Resume Next
Set allComments = Sh.Range("A1").SpecialCells(xlCellTypeComments)
If allComments Is Nothing And Not All Then MsgBox "No comments in " & ActiveSheet.Name: GoTo Ex
'// Leave On Error Resume next ON to take care of Loop not set error
'// when doing active sheet only.

For Each cCell In allComments
cCell.Select
cCell.Comment.Visible = True
cCell.Comment.Shape.Select True
With Selection
'// change interior color
.Interior.ColorIndex = 13
'// change font
.Font.Bold = True
.Font.Size = 12
End With
cCell.Comment.Visible = False
Next cCell
Next Sh
Ex:
Application.ScreenUpdating = True

Set allComments = Nothing
Set Sh = Nothing

End Sub</pre>

Part 2 of my post is :

Is there any way to change the default comment box, i.e. from the tahoma font etc.? I know one can change the default background fill colour by going onto the display settings, but seemingly, nothing else.

Please help.....if only for my cat's sake!

Thanks, in anticipation

Sykes

PS To all animal lovers......before you start chastising the MVP's etc etc, ..I havn't got a cat really!


_________________
....and the meek shall inherit the earth...
(but not the mineral rights!)
This message was edited by sykes on 2002-08-24 09:16

_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><font size=1> From the City of Sails

This message was edited by Ivan F Moala on 2002-08-24 10:31

 

[sykes]

Ivan

Im speechless.

Many thanks, as always

yours

In awe, of the UK!