PDA

View Full Version : Adding comments to a worksheet



cmm0812
01-06-2008, 05:50 PM
Is there a code that can be entered into this VBA to disable users from adding comments into locked cells?

'Code to be placed in the worksheet(s) you want to use this in
Option Explicit
Public oldRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
Dim rng As Range
Set rng = Target(1, 1)

oldRange.Comment.Visible = False

With rng
If Not .Comment Is Nothing Then
If .Comment.Visible = False Then
.Comment.Visible = True
Else
.Comment.Visible = False
End If
End If
End With

Set oldRange = Target(1, 1)
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error Resume Next
Dim cmtText As String
Dim inputText As String

If Target.Comment Is Nothing Then
cmtText = InputBox("Enter info:", "Comment Info")
If cmtText = "" Then Exit Sub
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
Else
If Target.Comment.Text <> "" Then
inputText = InputBox("Enter info:", "Comment Info")
If inputText = "" Then Exit Sub
cmtText = Target.Comment.Text & Chr(10) & inputText
Else
cmtText = InputBox("Enter info:", "Comment Info")
End If
Target.ClearComments
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
End If

Cancel = True 'Remove this if you want to enter text in the cell after you add the comment
End Sub