Replace comment box contents

newvbie

Board Regular
Joined
Mar 18, 2006
Messages
69
Hi there. I got a problem changing comment contents after users add the comments. I got the following code trying to delete the line of "Type comment here>:" in the comment box after user types his own comments. But it did not work.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not ActiveCell.Comment Is Nothing Then 
    Exit Sub 
Else 
    resp5 = MsgBox("Enter comments?!  Proceed? (Y/N)", vbYesNo + vbDefaultButton2, "Comments") 
    If resp5 = 7 Then 
        Exit Sub 
    ElseIf resp5 = 6 Then 
        Me.Unprotect Password:="xxxl" ', UserInterfaceOnly:=True
        Target.Locked = False 
        Target.AddComment 
        ActiveCell.Comment.Visible = True 
        addCom1="Enter comments here>" 
        addCom2=ActiveCell.Comment.text 
        ActiveCell.Comment.Text Text:=addCom1 & AddCom2 
        ActiveCell.Value = "Y" 
        ActiveCell.Comment.Text Text:=AddCom2 
    End If 
End If 
End Sub

Can anyone please help me out?

Cheers.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi

If you just want to remove "Type comment here>:" from the comments after a comment has been added... you can use this code. If the comment was modified it will replace the cell contents with a "Y".


Code:
Private last_cell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo normal_exit

    Dim strPrompt As String
    Dim strCmnt As String
    
    strPrompt = "Type comment here>:"

    'get the comment from the last cell
    strCmnt = Range(last_cell).Comment.Text
    
    'if no comment an error triggered and will resume at normal_exit label
    'else
    
    If strCmnt Like (strPrompt & "*") And Len(strCmnt) <> Len(strPrompt) Then
        Range(last_cell).Comment.Text Text:=Right(strCmnt, Len(strCmnt) - Len(strPrompt))
        Range(last_cell).value = "Y"
    End If


normal_exit:
    last_cell = Target.Address

End Sub
 
Upvote 0
Here is another way... the previous code needed the user to enter the cell with the comment. This code will go through each comment on the SelectionChange event and remove "Type comment here>:" if the comment has been modified. It also places a Y in the cell that the comment is linked to.

Why the two codes? If the comment is not visible you need to select the cell to edit the comment, so the previous code would work fine. If the comments are visible you do not need to enter the cell to edit the comment and so the previous code would not catch those instances, but the code below will.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

    Dim strPrompt As String
    Dim strCmnt As String
    Dim x As Integer
    
    strPrompt = "Type comment here>:"
    
    For x = 1 To Me.Comments.Count
        strCmnt = Me.Comments(x).Text
        If strCmnt Like (strPrompt & "*") And Len(strCmnt) <> Len(strPrompt) Then
            Me.Comments(x).Text Text:=Right(strCmnt, Len(strCmnt) - Len(strPrompt))
            Me.Comments(x).Parent = "Y"
        End If
    Next x

End Sub
 
Upvote 0
Hi WONG. Thanks for your codes. I've tried but it did not trigger anything. I mean, nothing changed. My application is intended to allow user to click on a cell and a comment box will appear beginning with "Type comment here>:". After user enters the comment, the cell value will be updated to "Y" and the line "Type comment here>:" will be deleted from the comment box. Can I do that by vba?

Cheers.
 
Upvote 0
OK... that's a little more info. The previous code was only looking for cells with comments "Type comment here>:" and if the comment had additional text would remove the prompt... Here is code that when a user selects a cell, will prompt the user to either add or modify the comment.


Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

    Dim strPass As String
    Dim strPrompt As String
    Dim strCmnt As String
    Dim blnAddCmnt As Boolean
    
    strPass = "xxxl"
    
    'if a comment exists
    If Not ActiveCell.Comment Is Nothing Then
        strPrompt = "Revise comments?! Proceed? (Y/N)"
        strCmnt = ActiveCell.Comment.Text
        blnAddCmnt = False
    'else no comments exist
    Else
        strPrompt = "Enter comments?! Proceed? (Y/N)"
        strCmnt = ""
        blnAddCmnt = True
    End If
    
    'check to see if user wants to add/modify comments
    If MsgBox(strPrompt, vbYesNo + vbDefaultButton2, "COMMENTS") = vbNo Then Exit Sub
    
    'get new/modified comments from user
    strCmnt = InputBox("Type comment here>:", "ENTER COMMENTS", strCmnt)
    
    'if currently no comment and user did not enter a value in the input box exit sub
    If blnAddCmnt And strCmnt = "" Then Exit Sub

    'unprotect worksheet
    Me.Unprotect Password:=strPass
    ActiveCell.Locked = False
    
    'if currently has comment and comment deleted from input box...
    'delete current comments and remove Y from cell
    If Not blnAddCmnt And strCmnt = "" Then
        ActiveCell.Comment.Delete
        ActiveCell.Value = ""
        ActiveCell.Locked = True
        goto normal_exit:
    End If
    
    'add comment if needed
    If blnAddCmnt Then ActiveCell.AddComment
    
    'enter comment into comment box
    ActiveCell.Comment.Text Text:=strCmnt
    ActiveCell.Comment.Shape.TextFrame.AutoSize = True
    ActiveCell.Comment.Visible = True
    ActiveCell.Value = "Y"
    
normal_exit:
    'protect sheet
    Me.Protect Password:=strPass
End Sub
 
Upvote 0
Oh dear. That's exactly what I want! Thank you very much indeed, WONG. One more question for you, please: When I open a file which is being opened by other user, it will prompt with a message box with options of "Read-only, Notify, cancel". Can this message box be suppressed/disabled and replaced by my own message box by vba?

Cheers.
 
Upvote 0
Not that I know of... sorry. If you need an answer try posting it as another subject.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top