none
Why Don't All Hyperlinks Get Hand-Icon on Entire Cell? RRS feed

  • Question

  • As you can see in the image, this hyperlinked cell displays a hand-cursor anywhere over the hyperlinked cell, even when hovering over an empty part of the cell. The cell is part of a Table (ListObject).

    However, this other two sheets do NOT display the hand icon on blank areas of the hyperlinked cell-- they display the white cell cross-hairs.

    Why? These sheets are both protected, and all cells are locked. Selection of both locked and unlocked cells is allowed on all sheets. In the last pic, the cell is part of a Table (ListObject), just like the first pic.

    The workbook can be downloaded here. Sheets can be protected/unprotected manually, or using the embedded macros.

    https://www.dropbox.com/s/3x0fyzkbgq22k3x/Pointer.xlsb

    thx


    • Edited by johny w Saturday, August 9, 2014 12:43 AM
    Saturday, August 9, 2014 12:42 AM

Answers

  • It "works" for me with formulas that return a number, a date or a logical value (TRUE/FALSE), but indeed not - as you have found - with formulas that return a text string.

    Added later: in fact, it doesn't have to be a formula. You'll get <<Selection in document>> and a hand cursor in the entire cell if the cell contains anything but a text value or a formula that returns a text value. Thanks to johny w for pointing this out.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, August 9, 2014 9:06 PM

All replies

  • more clues:

    comparing the links, i find two differences

    • the hand-on-full-cell link vba "TextToDisplay" property is blank; the other link has a value for that property
    • in the 'Edit hyperlink' window, the hand-on-full-cell link "Text To Display" property is not editable, and contains "<<Selection in document>>". That property on the other link is editable.
    Saturday, August 9, 2014 3:05 PM
  • If you add a hyperlink to a cell that contains a formula, the entire cell will act as hyperlink (you'll see the hand cursor if the mouse pointer is anywhere above the cell), and Display Text will contain <<selection in document>> and be grayed out.

    This will remain even when you replace the formula with its value, for example by Copy followed by Paste Values.

    So I suspect that cell D2 on the Control sheet originally contained a formula.

    Added later: in fact, it doesn't have to be a formula. You'll get <<Selection in document>> and a hand cursor in the entire cell if the cell contains anything but a text value or a formula that returns a text value. Thanks to johny w for pointing this out.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, August 9, 2014 7:40 PM
  • Does it have to be a special kind of formula?

    In the excel file linked above, first sheet A6, i hyperlinked a cell containing the following formula, and the whole cell did not get linkified as you describe-- only the text:

    ="test"

    thx

    Saturday, August 9, 2014 8:56 PM
  • It "works" for me with formulas that return a number, a date or a logical value (TRUE/FALSE), but indeed not - as you have found - with formulas that return a text string.

    Added later: in fact, it doesn't have to be a formula. You'll get <<Selection in document>> and a hand cursor in the entire cell if the cell contains anything but a text value or a formula that returns a text value. Thanks to johny w for pointing this out.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, August 9, 2014 9:06 PM
  • indeed.

    thx!!!!

    Saturday, August 9, 2014 9:11 PM
  • it seems after after hyperlinking the cell, you can enter a number or boolean, and the whole cell remains linkified-- it does not have to be the same value.

    but entering text changed the hotspot to the text only.

    can you think of a way to display text in a cell, but trick excel into treating it like a boolean or number?

    thx

    Saturday, August 9, 2014 10:03 PM
  • Would the following be acceptable?

    • Enter an arbitrary number in the cell. It doesn't matter which - 0, or 1, or 37...
    • Set the number format to the custom format   "Text you want to display"   i.e. the display text enclosed in double quotes.

    The cell will display the desired text, but the formula bar will still display the number that you entered (and that will also be the actual value of the cell).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 10, 2014 9:00 AM
  • Brilliant! That works!

    PS, i would adjust your first answer about hyperlinking: the entire cell will get linkified with ANYTHING in the cell whose VALUE is not a text value. It does not have to be a formula. So, to be accurate, your answer was not exactly correct as originally worded. Can you post a reworded answer?

    Tip: the number format can be set in VBA this way:

    Range("a1").DisplayFormat.NumberFormat = """Banana"""

    or simply:

    Range("a1").NumberFormat = """Banana"""

    It's necessary to surround the text with 3 double-quotes, because the quotes themselves are part of the number format. That would not be necessary with a dynamic, actual number-format (which is not a static string like "Banana"):

    Range("a1").NumberFormat = "0.##"

    Also, the displayed result can be retrieved with the following (useful in cases where the number format is an actual number-format:

    Range("a1").Text

    In other words, Hans showed that a cell has actually three different properties: Formula, Value, and Text. Text enables us to display a string to the user which is totally unrelated to the value or formula in the cell. Very cool! This opens up some new possibilities for my development work.

    Thx






    • Edited by johny w Monday, August 11, 2014 9:35 PM
    Monday, August 11, 2014 7:18 PM
  • You're correct - I will edit my earlier replies.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 11, 2014 9:13 PM
  • one last question: Why? Why doesn't excel linkify the whole cell with text values? It's inconsistent: our technique above is a workaround for this inconsistency. Is there a reason for it? Or is it just a bug?
    Monday, August 11, 2014 9:20 PM
  • You'd have to ask the Excel development team, but it's unlikely that you'd get an answer.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 11, 2014 10:16 PM
  • Office 365 - select hyperlinked cell > right click > select 'Format cells' > select 'Wrap Text'

    Worked for me.

    • Proposed as answer by OtherGuy Tuesday, July 9, 2019 11:45 PM
    Tuesday, July 9, 2019 11:44 PM
  • Office 365 - select hyperlinked cell > right click > select 'Format cells' > select 'Wrap Text'

    Worked for me.

    Here to confirm that this also works in the non-O365 version of Microsoft Excel 2016 (in Office Professional package). Select the cell with the text hyperlink and turn on "Wrap Text". Suddenly the entire cell will be a hyperlink.

    Thank you OtherGuy for posting your way out of the woods!


    • Edited by mikey38654 Monday, October 21, 2019 8:21 PM
    Monday, October 21, 2019 8:21 PM