Default Comment Box

  • Thread starter Thread starter abee
  • Start date Start date
A

abee

How can I change the propoerties of the default comment
box

THank You

PS how does one get Ecel to use a user-defined template
as the default template when File|New is used
 
Here is a link that has a lot of information on comments.

http://www.contextures.com/xlcomments01.html

-------------------------------------------------------------------------------------------------------

I use a macro to create customized comments. Here is a stripped down
version. You can enter your comment in the InputBox. Depending on the
length of the comment, it will slice and dice to height and width.

-------------------------------------------------------------------------------------------------------

If you are not familliar with macros,

http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

-------------------------------------------------------------------------------------------------------

Sub InsertComment()
'' Inserts a custom comment.

Dim intH As Integer, intW As Integer
Dim strText As String

On Error GoTo errInsertComment

Application.ScreenUpdating = False

strText = Application.InputBox(Prompt:="Enter the comment here.",
Type:=2)
If strText = "" Or strText = "False" Then
strText = "": intH = 13: intW = 60
GoTo AddComment
End If

intW = Len(strText) * 5

Select Case Len(strText)
Case Is <= 50: intH = 13: intW = Len(strText) * 5
Case Is <= 100: intH = 26: intW = Len(strText) / 2 * 5
Case Is <= 150: intH = 39: intW = Len(strText) / 3 * 5
Case Is <= 200: intH = 52:: intW = Len(strText) / 4 * 5
Case Else: intH = 13
End Select

AddComment:

With ActiveCell
.AddComment
With .Comment
.text text:=strText
.Shape.Width = intW
.Shape.Height = intH
.Shape.Fill.ForeColor.SchemeColor = 15
End With
End With

Exit Sub

errInsertComment:

End Sub

HTH
Paul
 
Back
Top