Connecting cell comment with the other cell

[yakpol]

I am trying to set text of the cell comment being dependent on some other cell in the worksheet. This technique is easily achievable for text boxes (Select text box and type cell address in the formula bar) but not as straight forward for the comments. Wonder if anybody knows if it's possible. Thanks!

 

[TDAA]

Not sure if it is. The text boxes are defined elements within the sheet, therefor, making it easy to refer to them in macros and other elements in the sheet. This leads to the reverse being available, too.

Since the comment is not really meant to be used in the same fashion, I do not think they made it possible, but I will take a look.

 

[TDAA]

Should have looked before I opened my mouth. If using macros, try:

Range("E12").Comment.Text Text:=Range("E13").Value

Where E12 has the comment, and E13 has the text you want in it.

Not sure on straight out insertion though.

 

[yakpol]

Will need to tun macro every time to change the comment text. Wonder if permanent link is possible.

 

[TDAA]

When on the sheet, hit Alt-F11 to open the VBA. Select the Sheet where you want that comment to update and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("C5").Comment.Text Text:=Range("C6").Value
End Sub


Change the ranges as described before. This will function, and update when you change something on the sheet. You can check it by editing the comment to be blank, then changing something on the sheet.

 

[PeterStock]

One way to display different text in a cell based on cell values is to use IF for example:

=IF((D15>D16),"D15 is greater to D16","D15 is less than or equal to D16"

would show the first value in the cell if D15 was greater than D16.

Note this does not need a macro to run to show the correct text.

Peter Stockhausen
Senior Design Analyst (Checker)
Infotech Aerospace Services

http://www.infotechpr.net

 

[TDAA]

Peter he is looking to change a "comment" on a cell. I think the only way to do that is with code. You could change cell information that way, but still have the issue with getting that value into the comment. You cannot put the working formula into the comment box.

The last code I provided does not require a macro be run, but will update automatically, as the user uses the sheet. You will likely still have to enable macros to allow the VB code to run.

 

[yakpol]

Thanks guys. Seems like VBA is the only option.

 

[TDAA]

Yes, but at least the second option is not going to be intrusive to the user, it will all happen behind the scenes.

 

[Clyde38]

TDAA,
I'm using Excel 2007 and

Private Sub Worksheet_Change(ByVal Target As Range)

did not work.

I had to use the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Is this due to Excel 2007?

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[TDAA]

I had come up with that in Excel 2007. I had opened a new file, and started, so it was not an old file in compatibility mode. Was your file in compatibility mode?

I have noticed small changes like that between versions, but find it strange that it happened within the same version. It is Microsoft though.

 

[Clyde38]

I too opened a new file and I was not in compatibility mode.

The first rule of any technology used in a business is that automation applied to an efficient operation will magnify the efficiency. The second is that automation applied to an inefficient operation will magnify the inefficiency.

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[TDAA]

one thing I noticed when I set that up is that it did not work if there was not a comment present. It did work with a blank comment, as long as it was there.

 

[Clyde38]

Yes, I saw the same and added a comment to see if that worked. It did not until I modified to SelectionChange.

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[TDAA]

HMM, well, I was just grabbing at straws as to why it worked on mine without the change.

Thanks for posting up the alternate, especially if it seems to work more consistently.