AUTO UPDATE Cell results in comment box

[neills]

Please help me
how can i put the result of a cell into a comment box

with thanks
Neill

[ This Message was edited by: neills on 2002-05-25 12:26 ]

[ This Message was edited by: neills on 2002-05-27 07:06 ]

[ This Message was edited by: neills on 2002-05-27 09:28 ]

[ This Message was edited by: neills on 2002-05-27 09:29 ]

[smozgur]

Try this code in relevant Sheet's module:
(it is working for A1 cell in Sheet1 as you can see, you can modify Sheet name and Cell reference as you need)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
On Error Resume Next
Sheet1.Range("A1").Comment.Text Text:="Cell value is " & Chr(10) & Sheet1.Range("A1").Value
If Err.Number = 91 Then Sheet1.Range("A1").AddComment Text:="Cell value is " & Chr(10) & Sheet1.Range("A1").Value
End If
End Sub

Regards

[neills]

cannot seem to get this to work???

and dont know why????

[ This Message was edited by: neills on 2002-05-25 03:12 ]

[smozgur]

Insert a comment to Cell A1 in Sheet1 and change the A1 value.

you will see it.

[neills]

How do i put in the code???

I have gone to VBE created a macro copied the code into it but it will not run
what am i doing wrong???

Thanks for your patience!!

[Ivan F Moala]

On 2002-05-25 03:36, neills wrote:
How do i put in the code???

I have gone to VBE created a macro copied the code into it but it will not run
what am i doing wrong???

Thanks for your patience!!

The code is a Sheet event code.
1) Right click on the sheet tab you want
this action to happen in.
2) Select view code
3) paste code there.
4) go back to this page & see what happens
when you change A1

[smozgur]

I was offline, sorry.


Thanks Ivan ,

PS: Code uses Sheet1 so please use Sheet1 or Change the Sheet1 name in code as the sheet object name you want to use this code (you can see project name in VBA Project Explorer under Microsoft Excel Objects node)

[smozgur]

or use ActiveSheet instead Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
On Error Resume Next
ActiveSheet.Range("A1").Comment.Text Text:="Cell value is " & Chr(10) & ActiveSheet.Range("A1").Value
If Err.Number = 91 Then ActiveSheet.Range("A1").AddComment Text:="Cell value is " & Chr(10) & ActiveSheet.Range("A1").Value
End If
End Sub

[neills]

Thanks a lot
Works Great Now
and learned a bit more

neill

[smozgur]

Nice to hear that