command button macro

Posted by Milan on December 07, 2001 12:44 PM

I need a Command button on my Excel sheet to insert (only) a comment in the active cell, (not open the comment). After inserting the comment I want the Command
button to be disabled, like the Insert comment button on the Toolbar does.
I would also like another to open the comment for editng and another for deleting the comment from the cell.

Posted by Brabantio on December 09, 2001 5:25 AM

I can't imagine why you want to do this.
Is it not just as simple (or simpler) to right click the mouse to Add/Edit/Delete a comment ?

Anyway, here's three macros :-

Sub Add_Comment()
Dim C As Comment
On Error Resume Next
Set C = ActiveCell.Comment
On Error GoTo 0
If Not C Is Nothing Then
MsgBox "There is already a comment in the active cell."
Else
With ActiveCell
.AddComment "This is my comment"
.Comment.Shape.TextFrame.AutoSize = True 'Delete this line if you don't need to auto size the comment
End With
End If
End Sub

Sub Edit_Comment()
Dim oldC As String
Dim newC As String
With ActiveCell
On Error Resume Next
oldC = .NoteText
On Error GoTo 0
newC = InputBox(prompt:="Enter a comment. " & _
Chr(13) & "Selecting cancel will erase the comment", _
Default:=oldC)
.NoteText newC
End With
End Sub

Sub Delete_Comment()
ActiveCell.ClearComments
End Sub