Using an input box to select a cell and then using another input box to add a comment to that cell

[stleoc]

Hello,

I have been working on a simple calendar tracking spreadsheet which has many formulas and and other macros. Therefore, I must protect the file from accidental user deletions. There are some cells that are protected such that you can't even select them while others you can select but not edit.

That being said, there is a need to occasionally add comments to various cells from both types noted above. My biggest challenge has been working around the situation where someone clicks cancel at any time during the process (macro). I've already got the "delete comment" Macro working (shown below also for reference). I am having issues with the adding comments. I think there is obviously something going on after the first "end if", but I have not figured it out yet. Have a look and let me know your thoughts.

Code to delete comments: (seems to be working fine)
--------------------------------------
Sub Delete_Comment()Dim response1 As Range
On Error GoTo ErrorHandler
ActiveSheet.Unprotect
Set response1 = Application.InputBox(prompt:="Pick a cell.", Type:=8)

If response1 Is Nothing Then​

ActiveSheet.Protect​

Exit Sub​

Else
response1.Comment.Delete
ActiveSheet.Protect
End If
Exit Sub

ErrorHandler:
ActiveSheet.Protect
Exit Sub
End Sub
--------------------------------------------------------

Code to add comment: Not working so well
--------------------------------------------------------
Sub Comment()
Dim xselection As Range
Dim xcomment As String
On Error GoTo ErrorHandler
ActiveSheet.Unprotect
Set xselection = Application.InputBox(prompt:="Select a cell", Type:=8)
If xselection Is Nothing Then

ActiveSheet.Protect​

Exit Sub​

Else
End If
xTitleId = "Enter Comment"
xcomment = Application.InputBox("Input comments", xTitleId, "", Type:=2)
If xcomment = False Then

ActiveSheet.Protect​

Exit Sub
Else
xselection.AddComment
xselection.Comment.Text Text:=xcomment
ActiveSheet.Protect
End If
Exit Sub
ErrorHandler:
ActiveSheet.Protect
Exit Sub
End Sub
-----------------------------------------------------

 

[John_w]

The On Error GoTo line is hiding the error; delete or comment that line to see the error when you enter the comment.

Application.InputBox returns a Variant (causing the error), therefore change the declaration of xcomment to:

Dim xcomment As Variant

Alternatively, you could use the InputBox function instead of the second Application.InputBox. The InputBox function returns a String, therefore the change would instead be:

xcomment = InputBox("Input comments", xTitleId, "")
If xcomment = "" Then

PS please use CODE tags - the # icon in the message editor.

 

[stleoc]

Thanks. I went with the inputbox option. Thanks again.

Hello,

I have been working on a simple calendar tracking spreadsheet which has many formulas and and other macros. Therefore, I must protect the file from accidental user deletions. There are some cells that are protected such that you can't even select them while others you can select but not edit.

That being said, there is a need to occasionally add comments to various cells from both types noted above. My biggest challenge has been working around the situation where someone clicks cancel at any time during the process (macro). I've already got the "delete comment" Macro working (shown below also for reference). I am having issues with the adding comments. I think there is obviously something going on after the first "end if", but I have not figured it out yet. Have a look and let me know your thoughts.

Code to delete comments: (seems to be working fine)
--------------------------------------

Sub Delete_Comment()Dim response1 As Range
On Error GoTo ErrorHandler
ActiveSheet.Unprotect
Set response1 = Application.InputBox(prompt:="Pick a cell.", Type:=8)[INDENT]If response1 Is Nothing Then[/INDENT]
[INDENT]ActiveSheet.Protect[/INDENT]
[INDENT]Exit Sub[/INDENT]
Else
    response1.Comment.Delete
    ActiveSheet.Protect
End If
Exit Sub

ErrorHandler:
ActiveSheet.Protect
Exit Sub
End Sub

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

Code to add comment: Not working so well
--------------------------------------------------------

Sub Comment()
Dim xselection As Range
Dim xcomment As String
On Error GoTo ErrorHandler
ActiveSheet.Unprotect
Set xselection = Application.InputBox(prompt:="Select a cell", Type:=8)
If xselection Is Nothing Then[INDENT]ActiveSheet.Protect[/INDENT]
[INDENT]Exit Sub[/INDENT]
Else
End If
xTitleId = "Enter Comment"
xcomment = Application.InputBox("Input comments", xTitleId, "", Type:=2)
If xcomment = False Then[INDENT]ActiveSheet.Protect[/INDENT]
    Exit Sub
Else
xselection.AddComment
xselection.Comment.Text Text:=xcomment
ActiveSheet.Protect
End If
Exit Sub
ErrorHandler:
ActiveSheet.Protect
Exit Sub
End Sub

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