×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Connecting cell comment with the other cell

Connecting cell comment with the other cell

Connecting cell comment with the other cell

(OP)
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!

RE: Connecting cell comment with the other cell

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.

RE: Connecting cell comment with the other cell

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.

RE: Connecting cell comment with the other cell

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

RE: Connecting cell comment with the other cell

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.

RE: Connecting cell comment with the other cell

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
www.infotechpr.net

RE: Connecting cell comment with the other cell

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.

RE: Connecting cell comment with the other cell

(OP)
Thanks guys. Seems like VBA is the only option.

RE: Connecting cell comment with the other cell

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

RE: Connecting cell comment with the other cell

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?

View Clyde's profile on LinkedIn

RE: Connecting cell comment with the other cell

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. ;)

RE: Connecting cell comment with the other cell

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

Quote (Bill Gates):

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.
  

View Clyde's profile on LinkedIn

RE: Connecting cell comment with the other cell

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.

RE: Connecting cell comment with the other cell

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

View Clyde's profile on LinkedIn

RE: Connecting cell comment with the other cell

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources