MrExcel's Learn Excel #855 - Dynamic Comment?

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 7, 2009.
Sergiy asks if there is any way to have a cell comment or a validation input box reflect a value from a formula in a cell. I couldn't find a good solution, other than using VBA. Episode 855 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Question today from Sergey, and I can't quite solve it-- I can't find a satisfying answer.
Sergey says, "Hey, you know how we have cell comments?" So, we do Insert, Comment, and you have a little, you know, some text that pops up every time that someone hovers over the comment.
He says, "That's pretty cool, but how can I make that text be dynamic, to grab the value from another cell or, you know, a formula on the worksheet?" And, you know, I tried a few things: I tried to go in and set up the comment to point to another cell and that didn't work; I even tried to edit the comment; and then tried all sorts of things here, like you can set an auto shape up to show a formula; and I even tried to paste the camera tool into here, and just simply couldn't find a good way to have that work.
Now, you know, the auto shape-- you can have a regular object and, basically, in here, type a formula of =G6, and so that will update every time we recalculate the spreadsheet.
It will update what's in the text box.
I thought, well, you know, if we could access that text box in the comment and, you know, have it point to a formula-- but that just didn't seem to work.
So, I guess I'm kind of left with a Macro.
In the Macro, we actually came into the code pane for this worksheet.
From the top left drop-down, choose Worksheet; from the top right drop-down, choose Calculate; and then, what I did here, is I used the input message.
So, enter Validation.InputMessage, and so that it's equal to a certain value.
So, basically, what happens is, every time we press the F9 key, the worksheet recalculates and then this cell down here is updated, so it's 10:12:54.
If I would press F9, and then click in again, the tool tip that pops up is 10:13:03.
We could use the same concept to change the comment code-- every time that something calculates, it goes in and updates it.
And it doesn't seem to be a huge lag.
I mean, we can go through and enter numbers.
So we just recalculated the spreadsheet five times-- that macro ran five times-- and there wasn't any visible flash or anything like that.
So, you know, I'm not satisfied with this, we had to use some VBA to do it.
If anyone knows a way to actually get to that comment box and change dynamically, please drop me a note and we'll talk about that next week.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
 

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top