Displaying a string in a blank cell !

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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 :o:

Reply | Quote

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

Reply | Quote

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 ; :rolleyes:

Reply | Quote