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
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
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]
Originally Posted by mbarron
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?
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
Originally Posted by 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
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]
It makes sense but when I put it into practice it doesnt seem to work
In what way doesn't it seem to work?
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]
You changed the line around. try the following
[vba]Sub Comment()
Range("D1") = Range("A1").Comment.Text
End Sub[/vba]
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
I keep getting a Run Time Error saying the Object Variable or With Block variable not set, what do you think this may mean?
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.
I am only using the code you provided me in a blank workbook
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?Originally Posted by 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]
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.Originally Posted by mbarron
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]
I was going off of the requirements in your first post -
and hadn't noticed that your second post had reversed the request tocell comments to display information in other cellsI just noticed post #15. Unfortunately my post 16 was still in response to post 13.have the comment in its normal position but to read and display information from another cell on the sheet
Long story short - I'm glad things finally got sorted out for you.
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]