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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
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
 

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
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
 

newvbie

Board Regular
Joined
Mar 18, 2006
Messages
69
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.
 

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237

ADVERTISEMENT

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
 

newvbie

Board Regular
Joined
Mar 18, 2006
Messages
69
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.
 

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
Not that I know of... sorry. If you need an answer try posting it as another subject.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,038
Messages
5,545,672
Members
410,697
Latest member
srishtijain0708
Top