MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do you change the comment box when inserting a comment using VBA?


Posted by Chris Rock on July 11, 2001 9:42 AM

I have a macro that inserts a comment in the active cell. It is very simple.

I want to know how I can manipulate the size of the comment box, and how I can make certain text bold (if possible) within the comment, and how to insert a line break within the comment box.

Any ideas?


Posted by Joe Was on July 11, 2001 12:25 PM

Some of the many way Excel will let you comment by code.

Excel has a multitude of ways to comment. Some do not lend themselves Font or Size changes others do. Below are some of the ways you can comment. The "Input Validation" code by default has the title of the comment box Bold but you cannot change the text font. The formula text comment method will allow you to change the cell font to bold. See below. JSW

Sub myComment()
'Add Comment to a cell range by "InPut Validation Message."
'This code is run by Hot-key = Ctrl-c
'Range("the cell address you want to comment").Select
Range("A1:A2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Your title here!"
.ErrorTitle = ""
.InputMessage = "Your Msg. here!"
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Sub myTextCom()
'Add comment by "Scenario" for cells "D1 & D2."
'This code is run by Hot-key = Ctrl-t

ActiveSheet.Scenarios.Add Name:="myComm", ChangingCells:=Range("D1:D2"), _
Values:=Array("This is one Comment!", "This is Comment two!"), Comment:= _
"Created by Joe Was", Locked:=True, Hidden:= _
False
End Sub

Sub showMyComm()
'This will display the comments, loaded by "Sub myTextCom()" as the scenario(myComm)!
'This code is run by Hot-key = Ctrl-s

ActiveSheet.Scenarios("myComm").Show
End Sub

Sub delTexComm()
'This will delete the scenario(myComm)comments displayed by "Sub ShowMyComm"!
'This code is run by Hot-key = Ctrl-d

Range("D1:D2").Select
Selection.ClearContents
Range("D3").Select
End Sub
Sub addCellText()
'This code will simply add text to the selected cell.
'This code is run by Hot-key = Ctrl-x

'Comment-out to run for range!
ActiveCell.Select

'This code selects a cell range rather than the active cell range above!
'Range("E1").Select

ActiveCell.FormulaR1C1 = "This is one Comment!"

'Bold the comment.
ActiveCell.Font.Bold = True

'Range("E2").Select
'ActiveCell.FormulaR1C1 = "This is Comment two!"
'Bold the comment.
'ActiveCell.Font.Bold = True
End Sub

Sub sExcelCom()
'This code will display an excel comment.
'This code is run by Hot-key = Ctrl-e

With Range("E1").AddComment
.Visible = True
.Text "Cell comment E1"
End With
End Sub
Sub rExcelCom()
'This code will hide the Excel comment!
'This code is run by Hot-key = Ctrl-r

Range("E1").Comment.Visible = False
End Sub