Manipulate Cell Comments

[Apprentice88]

Hi,

I am new to Visual Basic and would appreciate any help.

How do I manipulate cell comments to display information in other cells?

Thanks,

The Apprentice

[mbarron]

This will place the comment in the currently selected cell into the cell immediately to the right of that cell.
[VBA]Selection.Offset(, 1) = Selection.Comment.Text[/VBA]

[Apprentice88]

This will place the comment in the currently selected cell into the cell immediately to the right of that cell.
[VBA]Selection.Offset(, 1) = Selection.Comment.Text[/VBA]

What I was looking for was to have the comment in its normal position but to read and display information from another cell on the sheet, is it possible?

[ioncila]

Hi

I have posted almost at same time (funny ) a similar question and it has the right code for your issue too provided by LINNNOW.

If You look for the thread "get comment text in another cell" I guess you'll find the help you need.

Cheers
Ioncila

[Apprentice88]

Hi

I have posted almost at same time (funny ) a similar question and it has the right code for your issue too provided by LINNNOW.

If You look for the thread "get comment text in another cell" I guess you'll find the help you need.

Cheers
Ioncila

Thanks Ioncila, yes it was a strange coincedence lol, the code that LINNNOW kindly provided is not suitable for my application though because what I really need is for the comment to basically print out whatever it reads in a certain cell somewhere else on the sheet

[mbarron]

You can use the code I supplied as an example:

This will place the text from the comment in D1 in A1

[vba]Range("A1") = Range("D1").Comment.Text [/vba]

[Apprentice88]

It makes sense but when I put it into practice it doesnt seem to work

[mbarron]

In what way doesn't it seem to work?

[Apprentice88]

When I place the code in the module or the sheet it gives me nothing

[VBA]
Sub Comment()
Range("A1").Comment.Text = Range("D1")
End Sub
[/VBA]

[Aussiebear]

You changed the line around. try the following

[vba]Sub Comment()
Range("D1") = Range("A1").Comment.Text
End Sub[/vba]

[Apprentice88]

I keep getting a Run Time Error saying the Object Variable or With Block variable not set, what do you think this may mean?

[mbarron]

It means you need to show us the actual code you are using and possibly a sample of the workbook you are using as well. Replace sensitive data if necessary.

[Apprentice88]

I am only using the code you provided me in a blank workbook

[mbarron]

Do you have a comment in the cell? You'll receive the error if there in no comment connected to the cell.

[Apprentice88]

Do you have a comment in the cell? You'll receive the error if there in no comment connected to the cell.

No I do have a comment in the cell but its reversed, its displaying the information of the comment into the cell rather than displaying the information of the cell into the comment, see what I mean?

[mbarron]

You can use something like this to determine if there is a comment in the cell.
If there is no comment the destination cell will show "no comment"

[VBA]Sub HasComment()
On Error Resume Next
Range("a1") = Range("d1").Comment.Text
If Err.Number = 91 Then
Range("A1") = "no comment"
End If
Err.Clear
End Sub[/VBA]

[Apprentice88]

You can use something like this to determine if there is a comment in the cell.
If there is no comment the destination cell will show "no comment"

[VBA]Sub HasComment()
On Error Resume Next
Range("a1") = Range("d1").Comment.Text
If Err.Number = 91 Then
Range("A1") = "no comment"
End If
Err.Clear
End Sub[/VBA]

Thanks but I think you took me literal, I know the comment exists and its visible, I was trying to find a way for the comment to display information from another cell on the sheet, If you can please help me I would appreciate it. Thank you.

[Apprentice88]

Thank you all for helping me figure this out, A kind user from another board answered my question and here is the solution:
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1").Comment.Delete
Range("A1").AddComment
Range("A1").Comment.Visible = False
Range("A1").Comment.Text CStr(Range("A2").Value)
End Sub[/VBA]

[mbarron]

I was going off of the requirements in your first post -

cell comments to display information in other cells

and hadn't noticed that your second post had reversed the request to

have the comment in its normal position but to read and display information from another cell on the sheet

I just noticed post #15. Unfortunately my post 16 was still in response to post 13.

Long story short - I'm glad things finally got sorted out for you.

[mikerickson]

You could use this UDF.
If you put =SetComment(A1, B1) in a cell, then the comment in B1 will show the value of cell A1.

[VBA]Function SetComment(commentString As String, Optional cellOfComment As Range) As Boolean
Dim Flag As Boolean
On Error GoTo Halt

If cellOfComment Is Nothing Then Set cellOfComment = Application.Caller
If commentString = vbNullString Then commentString = " "
Set cellOfComment = cellOfComment.Cells(1, 1)

cellOfComment.Comment.Text commentString
SetComment = True

Exit Function
Halt:
If Err = 91 And Not Flag Then
cellOfComment.AddComment
Flag = True
Resume
End If
End Function[/VBA]