I downloaded a Forecast template via the new worksheet option. Found that some cells have a Comment as to what to enter in the cell. I can't seem to be able to delete or change the comment. Have tried to right click to hide, delete but nothing happens. Are such comments protected or something. If not how does one change or delete such comments.
Note comment does not seem to be the type enterd using comment option!

Hi there.

I found some code that copies a comment box into another cell as text

Function MyComment(rng As Range)
Application.Volatile
Dim str As String
str = Trim(rng.Comment.Text)
'// If you want to remove Chr(10) character from string, then
str = Application.Substitute(str, vbLf, " ")
MyComment = str
End Function

But I really want to copy a comment box into another cell as a comment box, not just as text. Even better, I want to copy both text and comment from a source cell into a destination cell.

Can anyone tell me how to modify this accordingly?

I know I can do this by simply cutting the source box, and pasting it into the destination cell, but this will get pretty tedious given I have a zillion cells to deal with.

So what I want to do is say have a custom formula...say "=comment(name_ref)" that will point to whatever cell corresponds to name_ref, and then reads the text in the cell and the comment box from that cell and copies both of these to the cell that the formula is entered into.

This will also mean that the comment information isn't hardwired into a zillion different cells on the spreadsheet...i only need to update the master comments box once, and every other cell pointing to it will be updated.

Thanks for any help or pointers. I am new to macros, so try to keep anything you post as simple as possible.

Hi all, I have a macro that inserts a cell comment into the active cell, and I have adjusted the shape and font to my needs, but... what i want to be able to do is then select the cell with added comment and using same macro (ideally) append new text to the comment preserving the original text.

So ctrl K inserts new comment if no comment exists but ctrl K also opens the comment in edit mode if it already exists. Do you see?

The macro as I have it is below, any help would be much appreciated.
I await your excel mastery!

Code:

Sub AddNewCom()
     
    Dim strCommentName As String
    Dim cmnt As String
    Dim Today
    Today = Now
    cmnt = InputBox("Please enter a comment")
    strCommentName = "User:    " & cmnt & vbLf & Now
    On Error GoTo 0

  
    If Not activeCell.Comment Is Nothing Then GoTo 0
     
    With activeCell.AddComment(strCommentName)
        .Visible = False
        .Shape.AutoShapeType = msoShapeRoundedRectangle
        With .Shape.TextFrame
            With .Characters(0, Len("Greer:")).Font
                .Bold = True
                .Italic = True
                .ColorIndex = 3
            End With
        End With
    End With
    With activeCell
        .Comment.Shape.TextFrame.AutoSize = True
        .Comment.Visible = False
    End With
    
    Exit Sub
     
0:
    
    If Err.Number  0 Then MsgBox Err.Description
     
End Sub

I have a spread sheet were the area is getting very limited. I need to insert a small icon and when the mouse goes over (like it does in a form tool tip) will show the value of a cell (text value) located in another sheet in same workbook, or I was thinking inset a comment next to the icon and link the comments of the comments text to cell with the text value.

I've look the properties of this to objects and can figure it out.

Any help will be greatly appreciated.

Hi,

I have an excel sheet which has comment box where the users go in and add project comments on the relevant cells. I have created a macro which copies(exports) all these comments from the comment box to Word. I need a macro which will highlight only the latest comments which were added on the comment box when exported to Word. ( So all the new additions will be highlighted in word).Please refer to the macro which currently exist.

Cheers!

Public Sub CopyActiveCommentsToWord()
Sheets("Change Calendar").Unprotect "mi"
Dim cmt As String
Dim C As Range
Dim I As Integer
Dim WordObj As Object
Dim ws As Worksheet
Dim PrintValue As Boolean
Dim res As Integer
On Error Resume Next
Err.Number = 0
res = MsgBox("Do want to view comments in Word?", _
vbYesNoCancel + vbQuestion, "Print Cell Comments")
Select Case res
Case vbYes
PrintValue = True
Case Else
Exit Sub
End Select
Set WordObj = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set WordObj = CreateObject("Word.Application")
Err.Number = 0
End If
WordObj.Visible = True
WordObj.Documents.Add
With WordObj.Selection
.TypeText Text:="All Cell Comments in Sheet: " + ActiveSheet.Name
.TypeParagraph
.TypeText Text:="Date: " & Format(Now(), "dd-mmm-yy hh:mm")
.TypeParagraph
.TypeParagraph
End With
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
For I = 1 To ws.Comments.Count
Set C = ws.Comments(I).Parent
Select Case C.EntireRow.Hidden + C.EntireColumn.Hidden
Case 0
cmt = ws.Comments(I).Text
With WordObj.Selection
If PrintValue = True Then
.TypeText Text:="Cell String: " & CStr(C.Address(0, 0, xlA1))
.TypeParagraph
.TypeText Text:="Project Type: " & Format(C.Value)
.TypeParagraph
.TypeText Text:="Project Name: " & C.Offset(0, 1).Value
.TypeParagraph
.TypeText Text:="Project Owner: " & C.Offset(0, 2).Value
.TypeParagraph
.TypeText Text:="Project Description: " & C.Offset(0, 4).Value
.TypeParagraph
.TypeText Text:="Key Dates: " & C.Offset(0, 3).Value
.TypeParagraph
.TypeText Text:="Issues/Mitigants: " & C.Offset(0, 7).Value
End If
.TypeParagraph
.TypeText Text:=cmt
.TypeParagraph
.TypeParagraph
.TypeParagraph
End With
End Select
Set C = Nothing
Next I
End If
Next ws
Set WordObj = Nothing
MsgBox "Finished Printing Comments To Word", vbInformation, _
"PrintCellComments"
Sheets("Change Calendar").Protect "mi"
End Sub

I am trying to take cells with comments and copy the comments into an adjacent column (as cell text, not comments) to improve readability and allow for editting. I do not want to manually do this or print the comments on the sheet in the boxes.

I tried the following Macro after consulting this board.

Sub ConvertComment()
'
' ConvertComment Macro
'
For i = 1 To 20
Cells(i, 4) = Cells(i, 3).Comment.Text
Next i

End Sub

However I get a run time error 91 (Object Variable or With Block Variable Not Set).
Any help to fix this Macro or suggest another way of doing the above task will be appreciated.
Thanks
Peter

Hi,

I am trying to find a way to obtain the text from a comment in a cell. I need this to work similar to a lookup since the cell address will be dependant on the project number (to give the row) and the column header (to give the column).

I found a UDF on Ozgrid;

http://www.ozgrid.com/VBA/ExtractCommentText.htm

Which is cool but I just can't figure out how to create the coordinates formula to give the column and row number.

Any ideas?

Good Afternoon

I have a worksheet and each cell has a comment attached to it and I need to change part of the comment text in each. This would take a significant amount of time to do cell by cell so I would like to change them all at once (as you can with find/replace which doesn't work on comments). Can anyone please advise if there is a way to do this. I just need to find all instances of one particular word and change it to another.

Thanks

VC

Is there a way to display the comment, attached to a cell, as the
'value' of the cell?

I have copied/pasted the comments to a new form (sheet) and would like
to have the comment text displayed IN the new cell. Then I can print
the sheet.

My current macro copies all comments from one sheet and pastes them
onto a second sheet. Now the new sheet shows the comment indicator,
with a blank cell. I want to replace the blank cell, with the TEXT of
the comment. Can this be done?

Thank you in advance.

JO

Greetings all.

I have just received a spreadsheet that has dozens of comments inserted all over the place (multiple cells on multiple sheets).

Would it be possible to dump all of these comments into cells on some particular sheet (one comment per cell obviously)? If so, anyone have any thoughts on how to best accomplish this?

I would prefer to not have to manually go in, show comments, copy the text and then paste that text into a cell on a worksheet.

Thanks in advance.

Kind Regards,

Sean