=HYPERLINK and adding a Screen Tip based on a cell reference in the active worksheet

[hans302]

Hi I have the following formula in Cell D7 in a worksheet called 'Data'

Please Login or Register  to view this content.

and I believe there is an addition that I can make to add a Screen Tip based on a Cell Reference - Can anyone help?

I need the above formula to also show the Screen Tip based on the Cell Reference in this worksheet 'Data' with the the Text "Take me to..." and then the value of Cell C7

Hope this is a simple request?

Any ideas?

Regards
Hans

[JohnTopley]

like ...

=HYPERLINK("#'Home Page'!A4","Take me to " &C7) ???

[hans302]

No.... that just returns cell C7 with the Text "Take me to" added to C7 - I need it to be a "SCREEN TIP" so when you hover over the cell it displays the Text "Take me to "& the value in cell C7.

Sorry if I did not make that clear in my first post?

Regards
Hans

[dmg2016]

Hi Hans,

To get a custom Screen Tip you need to insert a hyperlink instead of using the hyperlink function.

Insert Tab-->Links-->Hyperlink or shortcut Control K
Link to--> Place in Document
Type the Cell Reference and Text to Display
Click ScreenTip: "Take me to..."

You could use VBA:

Please Login or Register  to view this content.

DMG

[hans302]

Thanks for the VBA
Unfortunately the 'Set cell_reference' only works within the current worksheet - I need it to access different worksheets - Is there a code for that?

The ScreenTip bit works fine !

Regards
Hans

[dmg2016]

I think this should work.

1. Change cell_reference from range to string
2. Remove range from around inputbox for cell_reference
3. SubAddress:= cell_reference

Please Login or Register  to view this content.

First input should be entered as SheetName!cell reference (Ex:Sheet1!a1) to move to another sheet.

DMG

[hans302]

That's great - I just needed top put ' around the sheet name for it to work (EG 'Sheet1!'a1)

One final ask.... Is there a way of setting the screen_tip as the activecell.text instead of the input box? i.e. ScreenTip:="Take me to " & CStr(screen_tip), _ where CStr(screen_tip) is the activecell.text

Thanks again
Hans

[dmg2016]

I think CStr(activecell) or CStr(activecell.value) should work. However, I didn't test it out.


DMG

[hans302]

Yes that worked !!

Code as follows

Please Login or Register  to view this content.

Thanks again
Hans