In my last post I used my patented rollover technique to create an effect similar to the one shown below:
Neat, huh? When you place your mouse over a cell, it changes color to show that you are selecting it. It's a true "rollover": no mouse-clicks required. In my last post, I show how that might be useful.
Wondering how I did it? Checkout this file:
Rollover Surprise.xlsm
Neat, huh? When you place your mouse over a cell, it changes color to show that you are selecting it. It's a true "rollover": no mouse-clicks required. In my last post, I show how that might be useful.
Wondering how I did it? Checkout this file:
Rollover Surprise.xlsm
Jordan,
ReplyDeleteI have always preferred to use an overlaid shape to highlight rather than CF, so I adapted this technique to work with a shape. One big benefit is that it doesn't need any of the helper data.
Just create a shape cell sized and fill it as required.
Use a mouseover formula of
=IFERROR(HYPERLINK(Rollover(ROW(),COLUMN()),""),"")
and the the Rollover UDF looks like
Public Function Rollover(row As Integer, col As Integer)
With ActiveSheet.Shapes("hilite")
.Left = Cells(row, col).Left
.Top = Cells(row, col).Top
End With
End Function
Hey Bob! Using a shape instead of conditional formatting definitely looks like it has some speed improvements on top of not having to make the helper table. Sometimes I use the rollover method in conjunction with allowing the user to click on a cell - usually handled through a worksheet_change event - but now I'm spinning my wheels on using a shape instead that will execute a macro when clicked.
DeleteAnyway - it's an honor to have you post to my blog. One of these days I'll make it across the pond to an Excel conference. First round at the pub will be on me.... ;)
Jordan,
DeleteI do hope you can make it, myself and Andy pope are quite frequent visitors to the pub :).
Whilst I have used the Hyperlink function to allow changing other cells on a worksheet before, this rollover technique is a superb extension, I can't stop trying to shoe-horn it into previous applications. When I saw the periodic table I was so impressed I immediately sent it over to my daughter, a BioChemistry PhD student at Cantebury Uni in New Zealand. Great stuff! Don't you just love Excel?
Excel IS great! I think using a shape to handle a click (the shape would have to be semi-opaque) may prove a better technique in some circumstances. For example, because I use the selection_Change event for the periodic table, clicking on an already selected filter won't immediately turn it off because, well, the selection hasn't changed - no macro is fired.
DeleteBut I'm sure glad to hear you are trying to incorporate the rollover into your work. I'd love to take a look at anything you can send me.
My hope too (perhaps a pipe dream), is that Microsoft will see this (really, I'm just a nobody blog - I'm relying on the big ones to spread the message) and consider creating a more formal method to provide both a mechanism for "highlighting" and "details on demand." As it stands, using the hyperlink formula isn't terribly intuitive and is, at times, a bit cumbersome. I can't help but think I'm simply taking an advantage of a bug in Excel by using something in way that it was not intended.
Yes, the transparency level of the fill colour iin the shape needs to be at least 50%, probably closer to 75%, in order to see any cell contents - getting close to glass windows here :).
DeleteI have played a little with it and created a generic row/column highlighter. I had one already using CF, but I adapted it to this technique. It allows specifying a range that the highlighting is contained within, for example to keep it within a table, whole rows and columns, or neither which just highlights the moused over cell. ALl of this is configurable to each sheet or the whole workbook.
I agree though, it is pushing at a door that was probably inadvertently left open. Unfortunately, MS's current approach seems to lock down more than open up. It must be possible, they are very big on the concept of live preview, look how much has been built into Excel 13.
Those UDF arguments should be type Long not Integer.
ReplyDeleteNo longer working?
ReplyDeleteExcel 2013 → only valrow changes, not valcol?
Regards
Brian