Text contents from a cell in another tab to update comments on another tab in another cell

Hi there, I've seen some VBA code that lets me take the text contents of a cell and make them into a comment on that cell. What I cannot seem to figure out is:

How to take the text contents of cell (ex: Tab3 Cell-B1 has "wakka wakka" in it)
and then have "wakka wakka" appear as a comment on a different tab, in a different cell (ex:Tab1 Cell-D1).

Further - The comment should act like a forumla and automatically update the comment on Tab 1 Cell D2 to always reflect the current text in Tab3 Cell B1.

Help?

link cell D1 in Tab 1 to cell b1 in tab3

cell d1 in tab 1 should read =tab3!b1

Thanks RCM - this gets me the text from one cell to another. I appreciate the help however this is not what I'm looking for. I want to get the text from one cell to appear as a comment on another cell. So that when the text in a cell is updated on one tab, the comment on another tab reflects that.

Please Login or Register  to view this content.

this is set so that if cell B2 in sheet1 is changed the comment in cell d1 in sheet2 changes too

RCM - that is it! It just needed one line to clear out the exisitng comment - - updated snipet for anybody who wants this. and THANKS RCM!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.column = 3 And Target.Row = 28 Then

With Sheets("sheet2")
.Range("D1").ClearComments
.Range("D1").AddComment
.Range("D1").Comment.Visible = True
.Range("D1").Comment.Text Text:=Target.Value
End With
End If

End Sub

Hey - I've got a follow up question for you RCM. Is there anyway to make either of these cells (source text or destination comment) dynamic - so say if I sort, or just cut and paste D1 to another cell - say C4 - the code doesn't care and the comment follows the cell move? 0_o

Nevermind RCM - managed to figure it out on my own - for any interested you can name the cell using the name Manager on the formula tab and then refer to that name in the VBA code

So if you named a cell WAKKA WAKKA using name manager the could would be:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.column = 3 And Target.Row = 28 Then

With Sheets("sheet2")
.Range("WAKKA WAKKA").ClearComments
.Range("WAKKA WAKKA").AddComment
.Range("WAKKA WAKKA").Comment.Visible = True
.Range("WAKKA WAKKA").Comment.Text Text:=Target.Value
End With
End If

End Sub

Ok - follow up to this a week later. This ended up working out great- except when I delete text from the host cell and leave it blank. It gives me an error then. So a question for the smart people again - Is there a way to clean up after if somebody goes and deletes the text in the cell? Like.... check to see if there is text in the cell, if there is, execute the code above, if there is not (somebody just deleted text from that celll ...) then delete the comment completely from WAKKA WAKKA so it was like I was never there???

I figured it out - this is probably the cleanest if anybody is interested

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.column = 3 And Target.Row = 10 Then

Set Rng = Sheets("YourSheetName").Range("WAKKA WAKKA")

If Target = "" Then
MsgBox "Put a message here if you want to warn anybody about the comment being removed"
Rng.Comment.Delete
Else
With Sheets("YourSheetName")
.Range("WAKKA WAKKA").ClearComments
.Range("WAKKA WAKKA").AddComment
.Range("WAKKA WAKKA").Comment.Visible = True
.Range("WAKKA WAKKA").Comment.Text Text:=Target.Value
End With
End If
End If
End Sub