Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a worksheet that is sort of long and I would like to create a text box shape with a legend inside about what various entries mean or cause. For example, N/A in a cell means absolute zero in another cell while 0 means the other cell will use a calculated value, etc. Anyway, I can create the shape with the appropriate text but I cannot keep it in a static position so that when I scroll down the worksheet the text ox is always visible. It will scroll rightoff the screen along with the worksheet.

    Is there any way to keep it visible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think that'll be easy.

    You could use a userform instead with its ShowModal property set to False. The userform will float above the worksheet but not scroll with it.
    You can place a label (or labels) on the userform to display the help text.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sounds good - I looked at the toolbars and can find no rference to a userform... only form controls. Where would I do the userform or is it sufficient to make the label control with its ShowModal property set to False?


    [quote name='HansV' post='768282' date='30-Mar-2009 09:27']I don't think that'll be easy.

    You could use a userform instead with its ShowModal property set to False. The userform will float above the worksheet but not scroll with it.
    You can place a label (or labels) on the userform to display the help text.[/quote]

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Post 346142 has a technique I used to add a textbox in the header and the left side to always display when the range changed. This could be adapted.

    It keys off the selectionchange event, which can be problematic since just scrolling with the mouse and scrollbar will not trigger a change, it requires the selection to be changed. This is an issue with XL VBA which does not have a scroll event to monitor.

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    An option that does not require coding is to use a datavalidation input message. You can select all the cells in the usedrange and set the datavalidation to allow "Any value" then put the message you want in the "Input mesage" box. when you select any cell the message will be visible. This method allows you to put different messages per row, per column, or even individual cells

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you - I believe the data validation will do exactly what I need. Funny, I have never used validation before.. gotta learn more about this.

    [quote name='sdckapr' post='768290' date='30-Mar-2009 09:47']An option that does not require coding is to use a datavalidation input message. You can select all the cells in the usedrange and set the datavalidation to allow "Any value" then put the message you want in the "Input mesage" box. when you select any cell the message will be visible. This method allows you to put different messages per row, per column, or even individual cells

    Steve[/quote]

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='768290' date='30-Mar-2009 17:47']An option that does not require coding is to use a datavalidation input message. You can select all the cells in the usedrange and set the datavalidation to allow "Any value" then put the message you want in the "Input mesage" box. when you select any cell the message will be visible. This method allows you to put different messages per row, per column, or even individual cells

    Steve[/quote]

    An interesting approach Steve! I tested this on a small range 100x100 expecting the filesize to grow considerably, but no. So I tested on an entire w/s, and no change to the size. Curious!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •