Modify insert comment code

[Damo10]

Hi,

I have the following code to insert a comment into a protected workbook which works fine, what I would like to do is modify it so that it will only allow the user to enter a comment in the following range instead of any cell on the sheet.

Range ("J8:DM5038")

Sub InsertComment()
Dim xCom As String
Dim User As String
Dim xName As String
Dim Rng As Range
Dim c As Range
 
Set Rng = Sheet2.Range(Sheet2.Range("E6"), Sheet2.Range("E" & Rows.Count).End(xlUp))
User = UCase(Environ("username"))
If ActiveCell.Comment Is Nothing Then 'Checks to see if the cell already has a comment
For Each c In Rng
    If UCase(c) = User Then xName = c.Offset(, -2)
Next c
xTitleId = "Add Comment"
xCom = Application.InputBox("Enter comment", xTitleId, "", Type:=2)
With Application
    .ActiveSheet.Unprotect Password:="staff"
    .ActiveCell.AddComment
    .ActiveCell.Comment.Text Text:=xName & ":" & Chr(10) & xCom
    .ActiveSheet.Protect Password:="staff"
End With
Else
ActiveCell.Comment.Delete
For Each c In Rng
    If UCase(c) = User Then xName = c.Offset(, -2)
Next c
xTitleId = "Add Comment"
xCom = Application.InputBox("Enter comment", xTitleId, "", Type:=2)
With Application
    .ActiveSheet.Unprotect Password:="password"
    .ActiveCell.AddComment
    .ActiveCell.Comment.Text Text:=xName & ":" & Chr(10) & xCom
    .ActiveSheet.Protect Password:="password"
End With
End If
End Sub

Regards,

 

[Eric W]

The comment is added to the active cell? If so, just add this line at the top of your macro:

If Intersect(ActiveCell, Range("J8:DM5038")) Is Nothing Then Exit Sub

You could add an error message if you want:

    If Intersect(ActiveCell, Range("J8:DM5038")) Is Nothing Then
        MsgBox "Invalid location for a comment."
        Exit Sub
    End If

 

[Norie]

You could add this right at the start.

If Intersect(ActiveCell, Range("J8:DM5038")) Is Nothing Then
    MsgBox "You can only insert comments in the range J8:DM5038!", vbExclamation
    Exit Sub
End If

 

[Damo10]

Perfect, thanks very much