PDA

View Full Version : Manipulate Cell Comments



Apprentice88
05-24-2010, 08:05 AM
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
05-24-2010, 08:31 AM
This will place the comment in the currently selected cell into the cell immediately to the right of that cell.
Selection.Offset(, 1) = Selection.Comment.Text

Apprentice88
05-24-2010, 08:37 AM
This will place the comment in the currently selected cell into the cell immediately to the right of that cell.
Selection.Offset(, 1) = Selection.Comment.Text


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
05-24-2010, 09:52 AM
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
05-24-2010, 10:12 AM
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
05-24-2010, 10:30 AM
You can use the code I supplied as an example:

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

Range("A1") = Range("D1").Comment.Text

Apprentice88
05-24-2010, 11:26 AM
It makes sense but when I put it into practice it doesnt seem to work

mbarron
05-24-2010, 11:40 AM
In what way doesn't it seem to work?

Apprentice88
05-24-2010, 11:56 AM
When I place the code in the module or the sheet it gives me nothing


Sub Comment()
Range("A1").Comment.Text = Range("D1")
End Sub

Aussiebear
05-24-2010, 04:18 PM
You changed the line around. try the following

Sub Comment()
Range("D1") = Range("A1").Comment.Text
End Sub

Apprentice88
05-26-2010, 08:44 AM
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
05-26-2010, 08:49 AM
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
05-26-2010, 09:23 AM
I am only using the code you provided me in a blank workbook

mbarron
05-26-2010, 09:29 AM
Do you have a comment in the cell? You'll receive the error if there in no comment connected to the cell.

Apprentice88
05-26-2010, 09:36 AM
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
05-26-2010, 09:38 AM
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"

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

Apprentice88
05-26-2010, 09:42 AM
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"

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

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
05-26-2010, 10:19 AM
Thank you all for helping me figure this out, A kind user from another board answered my question and here is the solution:

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

mbarron
05-26-2010, 11:01 AM
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
05-26-2010, 07:00 PM
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.

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

Apprentice88
05-27-2010, 07:52 AM
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.

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


Mike, I tried your method and it works great and it actually might be easier doing it that way since Im going to be using this function alot throughout the project thanks alot for your help and sorry for the late response.

mikerickson
05-27-2010, 09:47 AM
I'm glad that it helped.