Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Displaying a string in a blank cell !

    I know how to display something other than a cell contents - for instance I have a cell whose contents (used in a test) are S but it displays Select because the Custom Format for the cell is @"elect".

    I want to be able to display a string in a cell when its contents are in fact blank, but when I use the above form of Custom Format I just get a blank cell. Is this possible, or will I have to find another way to do what I'm trying to do ?

    Thanks

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Martin

    In the attached file I have 3 sample cells which display the string "Blank" when the cell is empty, or displays the contents of the cell.

    If this is what you want, I can explain how it's done (unless you work it out yourself)

    zeddy
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Nice solution Zeddy - thanks. I use those little Text Boxes a lot, but had forgotten that they would do this particular trick !

    A transparent Text Box over a cell, with a Macro assigned to it, can also be very handy.

    Cheers.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I expect you knew this, but I had a comment in the cell which is now covered by the text box !

    I got around that by putting the text I wanted to display into the Hyperlink tooltip field for the text box.

    Incidentally I discovered that you can also assign a macro, by pointing the Hyperlink at an unused cell and then using the Worksheet change event to watch for that cell getting the focus . . . and running the desired Macro when it does. Works a treat - sounds complex but isn't

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Martin

    The top-corner triangle cell comment indicator is very tiny, so you can just make the textbox slightly shorter than the cell width.
    Then you'll see the cell comment when you float over the displayed triangle indicator.

    But using the Hyperlink tooltip is a very useful method too.
    I have used the hyperlink-cell-change-event trick very successfully and it is amazing what you can do with it.

    'For the benefit of other Users, using the simple textbox trick:

    'An 'invisible' text box can be created using the TextBox feature on the Excel 'Drawing' toolbar.
    'Draw a textbox and anchor and size it to fit over the heading cell, then set the format of the
    'text box to 'no fill' and 'no lines'; you can then right-click and assign a macro to the textbox shape.

    'You can assign a single VBA routine to multiple 'invisible' text boxes.
    'This makes it easy to copy and paste the invisible text box to other cells without having to assign
    'different routines to each box (although each box could of course have its own routine).
    'Any common VBA routine assigned would then execute commands depending on where the text box was located.
    'For example, you could use invisible text boxes in each header cell to perform sorts and filters etc.

    'In the VBA routine assigned to the invisible text box, you can use the following:

    'to return name of textbox shape that was clicked by User:
    zBox = Application.Caller

    'now use name of textbox shape that was clicked to fetch cell address of the cell underneath the textbox e.g.$C$4:
    zAddress = ActiveSheet.Shapes(zBox).TopLeftCell.Address

    'column of textbox that was clicked:
    zCol = Range(zAddress).Column 'e.g. 3


    zeddy

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks for all that.

    My favourite is to put one of these transparent text boxes over a cell in which I record when I have done something - and assign a macro which puts the current date into the cell under the text box. Its just a touch lazier than CTRL ;

Posting Permissions

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