Add Tooltip to Shape with Macro to include Text and cell value

[ADawn]

Hi all,

Hoping someone might be able to assist. I am a novice to macros and I'm not sure if what I want is possible. I found the below code to add a toolTip to a shape. Is it possible to expend on this to also include some cell values?

For Example: My shape currently includes the tooltip (Mixing Pad) but I also want to include cell D2 value, possibly with D2 text

So the tooltip would show

Mixing Pad. Total 5

Thanks


Capture.JPG

Please Login or Register  to view this content.

[Norie]

All you need to do is get rid of the InputBox and put the values from the cells in toolTipText.

Please Login or Register  to view this content.

[ADawn]

Thankyou -That worked perfectly.

I noticed that the Tool Tip doesn't appear to be dynamic. I've updated the cell and it still has the original value - Is this expected?

[Norie]

Yes it is unfortunately, the code doesn't link the tooltip to the cells, it only takes the values from the cells to set the text for the tooltip.

[AliGW]

Administrative Note:

@ADawn


Please read the forum rules regarding code in your posts.

Also, your forum profile should be showing [B]the version of Excel that you need this to work for[/B, not Windows.

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[leelnich]

Hi all-
You could update the tooltip via a User-Defined Function that takes a range as input. Just call it with D2 as input; Excel will re-calculate it whenever D2 changes.
UDFs must be called from formulas; in the attached workbook, I just entered the following in cell D1 to ""calculate "" the Total column header...

Please Login or Register  to view this content.

... and set UPDATE_TOOLTIP to return TRUE:

Please Login or Register  to view this content.

Note that this Function merely updates an EXISTING hyperlink; it does not ADD one. Also, as is true of most VBA code, whenever the UDF is triggered, you may not be able to UNDO previous actions.

KEY POINTS to this approach:
A) Write the function in a standard Module. The return type and value can be whatever the calling formula expects.
B) Call the function from a formula, using the cell (or cells) you wish to track as parameter(s)