Manipulate Cell Comments

[Apprentice88]

Hi,

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

How do I make cell comments display information from other cells?

Thanks,

The Apprentice

 

[VoG]

Here's a simple example

Sub AddCmt()
Range("A1").NoteText Text:=Range("B1").Value
End Sub

 

[Apprentice88]

Thank you very much, How do i make this occur automatically without having to compile or run the macro?

 

[wigi]

For instance:

Sub f()
    Range("A1").AddComment
    Range("A1").Comment.Text Range("A2").Value
End Sub

 

[wigi]

Thank you very much, How do i make this occur automatically without having to compile or run the macro?

For instance, use a Worksheet_Change event like the one you can find in this forum for many thousand times already...

 

[VoG]

Thank you very much, How do i make this occur automatically without having to compile or run the macro?

Can you explain in more detail exactly what you want to happen.

 

[sulakvea]

BTW... you may wanna change Range("A1").NoteText Text:=Range("B1").Value to: cStr(Range("A1").NoteText Text:=Range("B1").Value). this way if the comment you're trying to add is entered as a number, Excel may bark at you.. also, if there's an existing comment in A1, you may wanna remove it first. this is what i would do:

Sub whatever()
 Range("A1").Comment.Delete
 Range("A1").AddComment
 Range("A1").Comment.Visible = False
 Range("A1").Comment.Text CStr(Range("A2").Value)
End Sub

 

[Apprentice88]

Can you explain in more detail exactly what you want to happen.

I am working on a spreadsheet that does calculations for my manufacturer and most of the sheet is based on formulas and circular references causing the cell information to always be updated according to the user input. I need the comments that I add to the "user input cells" to display information from other cells on the sheet without me having to manually run the macro.

 

[sulakvea]

if by "automatically" you mean when smth changes in the sheet - you enter this into a Sheet Module:

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

 

[Apprentice88]

if by "automatically" you mean when smth changes in the sheet - you enter this into a Sheet Module:

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

Thank you very much it works perfectly!