Introduction

Introducing Pretty Rollovers

Pretty Rollovers - Pretty Rollovers for Excel with Proper Tooltips and Macros

So what are Pretty Rollovers? They are Rollovers that have been, well, tidied up a bit I guess. My Pretty Rollovers have been a 'long' time in the making, these pesky Hyperlink Rollovers sometimes need a bit of work to tame! I have written and created over 25 different Rollover Workbook examples and included Rollovers in many Projects. Ever since Jordan Goldmeir pronounced the technique to the Excel/VBA world I have been obsessed with the methodolgy behind a Rollover and have learnt some amazing tricks using these little critters. This Project proves there is still stuff we don't fully understand about Rollovers or at the very least, that there are certain aspects of Rollovers that can be exploited. My Pretty Rollovers dive straight in at the deep end, picking up where I left off, when I completed 3 Sheets of Rollovers for my Dark Excel Software utilising the InverseIntersect Function and some nifty decision logic in the main Rollover Function

What do I get? Well, you get 2 Formulas that can be adapted to run Rollovers to 'RollOn' and to 'RollOff' a Cell, to Highlight the Rolled Cell, bring up a proper Tooltip, not just that ugly crud of a Tooltip that the Hyperlink Function creates and to link up to your Macros. Additional Code is supplied to get around the fact that following a Rollover certain actions like selecting other Sheets using VBA are blocked - yep, this Project has it all. The Rollover Code also gets rid of those tiny error triangles that the Hyperlink Function sometimes throws out at you too. Code Modules contain Code to support both Contiguous Pretty Rollovers and Non-contiguous / Spaced Pretty Rollovers. You can make your Rollovers exhibit the behaviou of a Button with a Depressed or Pressed look

For those of you not interested in purchasing my Software, but are keen to realise my method, the trick to creating this type of Rollover is to Delete or Clear a Range Hyperlink for the last Rollover and then recreate a brand new one on the fly for the Cell that is Rolled into. Think Excel isn't quick enought to do that? Think it will look jerky? Think again, it is as smooth as custard and lighteningly fast. Pull up your pants and brace yourself for something beautiful, I give you my Pretty Rollovers and I hope you blooming well love them!

Update: I have performed a deep-dive excercise looking at the Hyperlink Tooltip and considering also the '=HYPERLINK()' Function used with the Rollover technique. You can read all about what I discovered (and what I didn't) Here · Hyperlink Tooltip (deep-dive)

Did you know that I have discovered many different ways to run macros following Rollovers? I have now discovered and improved a robust method for allowing a Proper Tooltip and a Rollover. If you want to read about these different techniques and purchase my latest Hyperlink Rollovers Project then you can do so Here · Hyperlink Rollovers for Excel

Pretty Rollovers for Excel demonstrating using a proper Tooltip for a Hyperlink Rollover Function in a Cell · a proper Tooltip just like Mama used to make!

Pretty Rollovers can exhibit the look and feel of Pretty Buttons. You can change the Button highlight and background colour to anything that you want and pass it into the Rollover Function

A Pretty Rollover Button that is Depressed or Pressed in as we Toggle the headings - yep, believe it or not, this is just a Cell Rollover!

If you like this Project you may also enjoy

Hyperlink Rollovers · Pretty Buttons · Burger Menu · Rollover Burger Buttons with Tooltips & Animations · Flat UI · Small Toggle Buttons · Menubar · Toggle Buttons · xlui · Burger Buttons · Popup Burger Buttons

Download Other FREE Rollover Examples:
Rollover Example.xlsm (2 Sheets of Rollover examples)
Rollover.xlsm (alternative technique)
XYRollover.xlsm
Very Simple Rollover Tests w Click.xlsm (very simple Rollover Range examples that can just copied down)
Rollover Toolbar.xlsbm (embedded Icon-Works Font)
Rollover Toolbar Sheet Navigator using Font Awesome.xlsb (embedded Font-Awesome Font)
Menu.xlsm (embedded Glyphyx One NF Font)
Menu (fullscreen).xlsm (embedded Glyphyx One NF Font, Full Screen version)
Mint & Chocolate Rollover Buttons
Widget Rollover Buttons
UI Element Rollover Buttons
Metro Menu boilerplate.xlsm
Standard Report Schema (Rollover-enabled).xlsm
Business Report Schema (Rollover-enabled).xlsm
Colour Banding with Hyperlink Rollover.xlsm
Disable Cursor Movement in Menu.xlsm
juiicy.xlsx
Juiicy.xlsm
Juiicy.zip
Juiicy-Themes.zip
Delicious.xlsm
drop-down-menu-boilerplate-blue-multiple-menus.xlsm
Delicious.zip

Purchase my Software that demonstrate or use Rollovers to great effect:
Dark Excel
xlui

Prerequisites

Excel 2016 (32bit or 64bit)
Basic to moderate Excel Skills and some VBA skills to change your own Macros
Windows PC · NOT a Mac!
No support is provided for customization of this Software

Features

A nifty Excel Workbook with a Flower background and helpful links/advice
2 Codes Modules, one for Contiguous Buttons and another for Spaced buttons ie. a Button every other Column or Disparate Buttons
No Shapes or Images!
Rollovers can exhibit a Depressed or Pressed in look of a Button
The Text Property of the Cell is used to display a value - this is not affected by the Hyperlink and will not dissapear or change. It also allows us to NOT have to wrap the Rollover Functions in IFERROR() or to use any Text in the Friendly Link either
Hyperlink Text doesn't dissapear
Code Modules contain Code to support both Contiguous Pretty Rollovers and Non-contiguous / Spaced Pretty Rollovers
Pass in the RolloverRange colour and the RolloverCell colour (the one that highlights a Button) directly in the Rollover Formula meaning that you can have lots of Buttons and control all of their respective background and highlight Cell colours via the Hyperlink Function itself
Change the default Hyperlink and Followed Hyperlink Styles as each new Sheet is selected
On the Pretty Rollovers Sheet we turn on Tracking so that you can see what Button is being 'Rolled' over. You can disable this in the Code
Minimal Code when linking standard small Macros or tasks to a Rollover unless you want to do crazy things following a Rollover click and then you have to add a bit more Code
Click the Cell to run a Macro, click the cell again to run the Macro - there's no clicking away and then back again
No Jumping around to various dumb Cell References or Addresses, no '#' references, concise, neat and tidy actions
Simple 'RollOn' and 'RollOff' Formula containing all of the Parameters required to enact a Pretty Rollover
Pass in your Tooltip and Macro Name to run VBA Code
Rollover Buttons that can do anything (Pretty Rollovers Sheet in the Workbook) use a Timer to fire your Code Macro following a Click. The Timer is halted immediately but it allows the Hyperlink process to continue so your Code can do anything it usually does
Clear the Rollover Range within your Macro Code
Uses proper Tooltips not the Cell assigned Tooltip from the Hyperlink Function
RollOff does not use Tooltips
Uses the InverseIntersect function to colour a Rollover Range except the current Cell which is highlighted
Easily capture Mouse Clicks in the Rollover Range. Repeatedly Highlight and Click the same Cell to bring up your Macro - you don't have to deselect any Rollover Cells
One Formula that can be dragged across or down a Range to start your Rollover process
Use &CHAR(10) to create double-line Tooltips

Pretty Rollovers with proper Tooltips like your Mama used to make!

Installation

Open the PrettyRollovers.xlsm File and test out all of the Examples to see how they operate. Please read all of the info on the individual Sheets and in this online documentation before you begin to edit and create your own Pretty Rollovers or before turning to Support

Usage

PrettyRollovers.xlsm

Test out all of the Demos
Before you start editing and trying to create your own Pretty Rollovers, take some time to look at all of the Examples in all of the Sheets. The License Sheet contains some links to licensing, a link to the nifty background used by Pretty Rollovers (Moroccan Flower Pattern by: Tomislava Babić) and the online Help documentation. It also displays the 4 main colours used for Pretty Rollovers with the Long, HTML and RGB Colour Codes. The rest of the Sheets we will go through

The 'The Formula' Sheet
The 'The Formula' Sheet deep dives into the Rollover Formulas and contains all that you need to know about the Formula used for any of the Rollovers used in this Software. Start with this Sheet first to aquaint yourself with the general concept of the Rollover. If you need a debrief on the Rollover technique why not check out some of my examples at the top of the Introduction Page on this website, they are numerous and varying with classic examples and my many different adaptive Rollover techniques

The Formula Sheet deep dives into the Rollover Formulas used in this Software

The Formula Sheet deep dives into the Rollover Formulas used in this Software

There are 3 Code Modules with 3 Rollovers, very similar, but used for slightly different things. For example you can use a Rollover for a contiguous Range of Cells that can be copied and just dragged across or down a Sheet. Then you have a Rollover that can be disparate, meaning in different Cells or the next two Cells. Lastly you have a Rollover that can exhibit Button-like behaviour and uses a couple of extra lines of Code for Tracking. All of the Rollovers use a RollOff() or a Rollout() Rollover Function to reset or clear any Rolled over Cells within a Range

My Pretty Rollovers don't require any IFERROR() Functions or even the Friendly part of the Hyperlink Function. True I do use the IFERROR for the RollOff or RollOut Functions, but a future update may render this obsolete, I just haven't had time to further test and develop yet

Click on the 'Pretty Rollovers (Contiguous)' Sheet. Here you have the Pretty Rollover used for contiguous Ranges. One Cell is formatted and the Rollover configured, linked to a Macro and the background Cell Ranges and the Rollover Cell are chosen. The basic Rollover looks like this:

=HYPERLINK(Rollon($F15:$N15,F15,"This is a proper Tooltip","Macro1",8364426,11847866))

And the Code that reads this Rollover in, is like this:

' RollOn, adds a proper Tooltip and uses the InverseIntersect function to colour a Rollover Range except the current Cell which is highlighted
Public Function RollOn(ByRef RolloverRange As Range, ByRef RolloverCell As Range, ByRef Tooltip As String, ByRef Macro As String, ByRef RolloverRangeColour As Long, ByRef RolloverCellColour As Long) As Variant

You can quickly see the Rollover takes 6 Arguments (Args) or Parameters (Params). We pass the Range of Cells used by the Rollover as a Range, the Rollover Cell itself as a Range, the Tooltip as a String, the Macro to link as a String, a background Colour as a Long and a highlight Colour as a Long, simple. Deriving a Long Colour is easy-peasy. Just type ?RGB(255,13,4) in the VBA Immediate Window to get its Long value of 265727 and then stick it into your Rollover Function

The Basic RollOff Rollover Function looks like this:

=IFERROR(HYPERLINK(RollOff($F$15:$N$15,8364426)),"")

You can see that this Rollover takes 2 Arguments (Args) or Parameters (Params). We pass the Range of Cells used by the Rollover as a Range and the background Colour as a Long. This Function simply resets or clears the Rollover Range using the passed Colour

The 'Pretty Rollovers (Contiguous)' Sheet
This Sheet demonstrates the Pretty Rollovers that are used for contiguous Cells, Cells that are joined in a Row or a Column without breaks used to make psuedo Buttons. It means that the Rollover Formula can be copied to one formatted Cell and then just dragged across or down a Range (the down Formula does need its Range setting first). Here are the Pros and Cons of this Formula:

The Pretty Rollovers (Contiguous) Sheet demonstrates the use of the contiguous Rollover Formula used in this Software

The Pretty Rollovers (Contiguous) Sheet demonstrates the use of the contiguous Rollover Formula used in this Software

Pros
Simple RollOn and RollOff Formula
Pass in your Tooltip and Macro Name to run VBA Code
Clear the Rollover Range within your Macro Code
Uses proper Tooltips not the Cell assigned Tooltip from the Hyperlink Function
RollOff does not use Tooltips
Uses the InverseIntersect function to colour a Rollover Range except the current Cell which is highlighted
Easily capture Mouse Clicks in the Rollover Range
No more Jumping around all over the place
One Formula that can be dragged across or down a Range to start your Rollover process
Create double-line Tooltips using &CHAR(10)

Cons
You have to modify the hyperlink and followed hyperlink styles when you change Sheets if you want different Hyperlink Font and Followed Hyperlink Font

Roll along the first few Rollover Cells and hover over some of the Buttons to view their respective Tooltips. Click on some Rollovers and observe the results. A Macro will run with a simple Message. The Rollover will highlight as you roll over it before it is cleared by the RollOff() Rollover Function. Neat and you get a Tooltip that's a proper Tooltip not one of those jerkly little pretend Hyperlink Tooltips! Now scroll down the Sheet to see the same Formula but used for the contiguous Vertical Rollovers

The 'Pretty Rollovers (Spaced)' Sheet
This Sheet demonstrates the Pretty Rollovers that are used for disparate Cells, Cells that are not joined in a Row or a Column and that may be anywhere in your Workbook used make psuedo Buttons

The Pretty Rollovers (Spaced) Sheet demonstrates the use of the non-contiguous or disparate Rollover Formula used in this Software

The Pretty Rollovers (Spaced) Sheet demonstrates the use of the non-contiguous or disparate Rollover Formula used in this Software

It means that the Rollover Formula has to be adapted as we cannot use a normal Range. So how do we adapt the Rollover. Well we change the Range into a String, meaning that we can pass in the Rollover Range like this, here is the non-contiguous or Spaced Rollover:

=HYPERLINK(Rollon2("F15,H15,J15,L15,N15",F15,"This is a proper Tooltip","Macro10",8364426,11847866))

The Rollover Cell itself is left as a Range. So we have the same 6 Arguments or Parameters but now one of them is a String that we must configure. We only need to do this once though and then we can Copy the Formula into a formatted Rollover Cell as before and drag across or down the Sheet - it just means a little more work adding all of our disparate Cells. Now scroll down the Sheet to see the same Formula but used for the Spaced Vertical Rollovers

The 'Pretty Button Rollovers' Sheet
This Sheet demonstrates how Pretty Rollovers can exhibit Button-like behaviour. They can be Depressed or Pressed in as if something has been turned On or Off, handy to toggle things like the Headings on a Worksheet or the Ribbon for example. These Rollovers use the similar Spaced Rollover Formula but in a separate Code Module that also allow you to Track which Rollover or Button you are currently hovering over - check out the image below:

The Pretty Rollovers Sheet demonstrates the use of the Pretty Rollovers that act like Pretty Buttons and can be Depressed or Pressed in

The Pretty Rollovers Sheet demonstrates the use of the Pretty Rollovers that act like Pretty Buttons and can be Depressed or Pressed in

I also demonstrate how to use these Rollovers to do anything. By anything I mean that we override the limitations that using the Hyperlink Function throw at us. For example following a Hyperlink Rollover it is not easy to even select Cells and almost impossible to navigate to another Sheet. We get pass this limitation by using a Timer to fire a thread which we instantly kill. This brief firing allows us to continue as if the Rollover says, well I did my job, that's it, but doesn't really pay attention to us and we are then left alone to get on with what we want to do! Nice ;)

Two Tasks are demonstrated. One simply Inserts a Sheet and creates 250 Prime/Non-prime numbers and the second Inserts a Sheet and then runs a Cell Block animation. You can then delete both Sheets but it shows you that anything is now possible afte the Rollover is clicked. Watch the Video to see this in action

The 'Make a Rollover 1' and the 'Make a Rollover 2' Sheets
These two Sheets will hopefully show you how to Make a Rollover using my Functions and Code. Take some time to consider each Sheet and the details therein

The 'Make a Rollover 1' and the 'Make a Rollover 2' Sheets. This Sheet demonstrates the first 2 Rollover Functions

The 'Make a Rollover 1' and the 'Make a Rollover 2' Sheets. This Sheet demonstrates the third Rollover Function

Hyperlink Tooltip (deep-dive)

You can download the file that follows this article and includes all of the tests with full Code examples Here · Hyperlink Tooltip (deep-dive).xlsm

Did you know that I have discovered many different ways to run macros following Rollovers? This deep-dive is more around Shapes and the Hyperlink Tooltip. If you want to read about these techniques and purchase my latest Hyperlink Rollovers Project then you can do so Here · Hyperlink Rollovers for Excel

Let's look at the Hyperlink used to create a Tooltip for a Shape (I will refer to this as the Hyperlink Tooltip), that allows us to run a Macro at the same time and see if we can unlock some of its behaviour. I understand that a Function should be used instead of a Subroutine. I know that the Function should always use 'Set <Something> = Selection' to return an 'Object' or 'Range' that has some influences on its behaviour in terms of what can be ran using Code once it has been applied. The first thing I found out is that you can add a Hyperlink Tooltip to an Object like a Shape that can then run one or more Functions by using a comma-delimited list of Macro Names, to get 2 messages, "foo" and "bar", like this:

' AddTooltip1, used to add a Hyperlink and Tooltip to our Shape
' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip
Private Sub AddTooltip1()
   ActiveSheet.Shapes("Icon1").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon1"), "", "TestA(),  TestB()", ScreenTip:="This is a Hyperlink Tooltip deep-dive"
End Sub

Public Function TestA() As Variant
 Set TestA = Selection
 MsgBox "foo"
End Function
Public Function TestB() As Variant
 Set TestB = Selection
 MsgBox "bar"
End Function

The next thing that I found was that you could use the first Function (or Macro) called to call another Function, to get a single Message "foobar", like this:

' AddTooltip2, used to add a Hyperlink and Tooltip to our Shape
' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip
Private Sub AddTooltip2()
   ActiveSheet.Shapes("Icon2").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon2"), "", "TestC()", ScreenTip:="This is a Hyperlink Tooltip deep-dive"
End Sub

Public Function TestC() As Object
  Set TestC = Application.Run("TestD")
End Function
Public Function TestD() As Variant
  Set TestD = Selection
  MsgBox "foobar"
End Function

Realising that the retun value could be an Object or a Range, I then explored changing the return value as a Range to enable us to select another Sheet via the Hyperlink Tooltip and found that this worked to Select Sheet2 for Cell "A1". You can use the '[]' square brackets as well to do this Range select (see the commented out Code below). I alo noticed that if you now add a MsgBox to the below routine (I added this and it is commented out below) that the Message is sent to the Screen first and then the Range is selected for Sheet2, which makes sense as the Range is the Object returned to the hyperlink:

' AddTooltip3, used to add a Hyperlink and Tooltip to our Shape
' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip
Private Sub AddTooltip3()
   ActiveSheet.Shapes("Icon3").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon3"), "", "TestE()", ScreenTip:="This is a Hyperlink Tooltip deep-dive"
End Sub

Public Function TestE() As Range
  Set TestE = Sheet2.Range("A1")
  'Set TestE = Sheet2.[A15]
  'MsgBox "foobar"
End Function

One more test I wanted to check was whether I could pass a variable from inside the Hyperlink Tooltip. Well it turns out that you can, but it will only store a value at the moment that you set the Hyperlink, which makes sense I guess, meaning it doesn't query a variable again to find out its current value or property. I tried storing the Headings state of the ActiveWindow in a local variable to see if the variable changed whether the hyperlink Tooltip would pass the updated state but it only updates when the hyperlink is applied again. In example 6 I show a method to pass a variable into another Function via the first Function

Option Explicit

Private HeadingsState As Boolean

' AddTooltip4, used to add a Hyperlink and Tooltip to our Shape
' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip
Private Sub AddTooltip4()
   ActiveSheet.Shapes("Icon4").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon4"), "", "TestF(" & HeadingsState & ")", ScreenTip:="This is a Hyperlink Tooltip deep-dive"
End Sub

' Headings only changes if you run the AddTooltip4 Macro again, meaning it doesn't update in real time, which makes sense but is annoying ;)
Public Function TestF(ByVal Headings As Boolean) As Range
  Set TestF = Selection
  MsgBox Headings
End Function
Public Sub SetHeadingsState()
   HeadingsState = True 'False
End Sub

Okay, let's take a time out. So what have we learnt so far? Well we know we can run multiple Functions using the technique which may or may not be of any use at this point. We know that we can call another Function from the main Function using Application.Run - that's useful, we return an Object that is another Function (or Macro Name) that gets ran via the technique. We also know that we can select another Sheet by changing the return value to a Range and using the 'Set <FunctionName> = Sheet2.Range("A1")' or 'Set <FunctionName> = Sheet2.[A15]' - something that I have been unable to do in the past, hence the Timer technique whereby I fire a Timer thread to run another Subroutine and then kill it immediately, before continuing to run my Code. So can what we have learnt be put to use to negate the use of a Timer. Well that's my next Test. I will take one of my Pretty Buttons Toolbar Buttons and see if I can use these findings to make a workaround so that I don't use any API's, onwards...

Well it appears that trying a simple Task of Toggling the headings On/Off or adding a Sheet does not work using this technique. A workaround is to set a Cell which then fires the 'Worksheet_SelectionChange' event in a Worksheet Code Module, which to me is not a solution - I would rather use the Timer technique (SetTimer() can callback a regular function when fired) as you cannot re-fire the Code without deselecting or selecting another Cell in the Sheet. I demonstrate this in the fifth deep-dive test here:

' In a Worksheet Code Module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Address = "$A$1" Then
      ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings
   End If
End Sub

' in a standard Code Module
' AddTooltip5, used to add a Hyperlink and Tooltip to our Shape
' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip
Private Sub AddTooltip5()
   ActiveSheet.Shapes("Icon5").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon5"), "", "TestG()", ScreenTip:="This is a Hyperlink Tooltip deep-dive"
End Sub

' By selecting a Cell the Worksheet_SelectionChange event handler fires in the Worksheet Code Module to toggle the headings
Public Function TestG() As Range
  Set TestG = Sheet1.Range("A1")
End Function

I found that you can pass a variable form one Function into another Function like this (but this still doen't help us to execute the toggle headings code):

' AddTooltip6, used to add a Hyperlink and Tooltip to our Shape
' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip
Private Sub AddTooltip6()
   ActiveSheet.Shapes("Icon6").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon6"), "", "TestH()", ScreenTip:="This is a Hyperlink Tooltip deep-dive"
End Sub

' you can pass variables into another Function
Public Function TestH() As Range
   Set TestH = Application.Run("TestI", True)
End Function
Public Function TestI(ByVal Headings As Boolean) As Variant
  Set TestI = Selection
  MsgBox Headings
End Function

Let's now look at some other possibilities using Windows API. The first one I examine is the 'CallWindowProc' API passing the Procedure name into the API and we get our "foo" message but it still doesn't do much else, for example it fails on Sheet selection and toggling the headings:

Option Explicit

Private Declare PtrSafe Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As LongPtr, ByVal hWnd As LongPtr, ByVal Msg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr


' AddTooltip7, used to add a Hyperlink and Tooltip to our Shape
' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip
Private Sub AddTooltip7()
   ActiveSheet.Shapes("Icon7").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon7"), "", "TestJ()", ScreenTip:="This is a Hyperlink Tooltip deep-dive"
End Sub

' you can use the CallWindowProc API to call another Procedure but it still doesn't let you select or toggle the headings
' however you can use a Sub and you don't have to Set the Selection
Public Function TestJ() As Range
   Set TestJ = Selection
   CallWindowProc AddressOf TestK, Application.hWnd, 0&, 0&, 0&
End Function
Public Sub TestK()
   MsgBox "foo"
   Sheet2.Select
   ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings
End Sub

Okay, now let's look at a techique I found ages ago when writing my Booking System for Excel, that does actually work. I use the 'SetTimer' API to fire a Timer which I then instantly Kill when my Procedure runs. This allows me to do anything following a Hyperlink Tooltip or a '=Hyperlink()' Function Cell Rollover, like this (now we can Select other Sheets, toggle the headings, have a MsgBox etc. and still return the Selection for the Range Object):

Option Explicit

#If VBA7 And Win64 Then
   Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
   Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
   Private TimerEvent As LongPtr
#Else
   Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
   Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
   Private TimerEvent As Long
#End If

' AddTooltip8, used to add a Hyperlink and Tooltip to our Shape
' - ran once and then this does not need to be ever ran again unless you want to change the Shape or Tooltip
Private Sub AddTooltip8()
   ActiveSheet.Shapes("Icon8").Parent.Hyperlinks.Add ActiveSheet.Shapes("Icon8"), "", "TestL()", ScreenTip:="This is a Hyperlink Tooltip deep-dive"
End Sub

' using my Timer technique you can do almost anything following a Hyperlink Tooltip or a '=Hyperlink()' Function Cell Rollover
Public Function TestL() As Range
   Set TestL = Selection
   If TimerEvent <> 0 Then KillTimer 0&, TimerEvent
   TimerEvent = SetTimer(0&, 0&, 0, AddressOf TestM)
End Function
Public Sub TestM()
   KillTimer 0&, TimerEvent
   TimerEvent = 0
   MsgBox "foo"
   Sheet2.Select
   ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings
End Sub

So what about precedent? I mean what if you have a Hyperlink Tooltip in a Cell and a Rollover in the same Cell, what happens? Well I tested this here and found that the Hyperlink Tooltip will take precedent over the '=HYPERLINK' Rolover Function. Here is the Code that I used and all of this is included in the File that you can download, which will make more sense when you see it:

' i added the Hyperlink Tooltip to Cell "B37"
Private Sub AddRangeTooltip()
   ActiveSheet.Range("B37").Hyperlinks.Add ActiveSheet.Range("B37"), "", "TestN()", ScreenTip:="This is a Hyperlink Tooltip deep-dive"
End Sub

' I added the Rollover Hyperlink to Cells "B33" and "B37", '=IFERROR(HYPERLINK(Rollover(D33)),"#D33")', '=IFERROR(HYPERLINK(Rollover(D37)),"#D37")'
Public Function Rollover(ByVal RolloverCell As Range) As Range
   RolloverCell.Value2 = Rnd(999)
End Function

Public Function TestN() As Range
   Set TestN = Sheet2.Range("A1")
End Function

Conclusion
In conclusion I have made a few discoveries, but nothing comes close to using a Timer to allow a Macro, Function, Procedure (call them what you will) to run and do anything following a Hyperlink Tooltip or a '=Hyperlink()' Function Cell Rollover. Therefore until some other methodolgy is found I will continue with my method. I hope you like this deep-dive and found some useful concepts around the Hyperlink Tooltip when applied to Shapes

You can download the file that follows this article and includes all of the tests with full Code examples Here · Hyperlink Tooltip (deep-dive).xlsm

Screen Shots

The Formula Sheet deep dives into the Rollover Formulas used in this Software to make Rollovers with Proper Tooltips and Link Macros that can do anything

The Pretty Rollovers (Contiguous) demonstrates the use of the contiguous Rollover Formula used in this Software

The Pretty Rollovers (Spaced) demonstrates the use of the non-contiguous or disparate Rollover Formula used in this Software

The Pretty Rollovers Sheet demonstrates the use of the Pretty Rollovers that act like Pretty Buttons and can be Depressed or Pressed in

The 'Make a Rollover 1' and the 'Make a Rollover 2' Sheets. This Sheet demonstrates the first 2 Rollover Functions

The 'Make a Rollover 1' and the 'Make a Rollover 2' Sheets. This Sheet demonstrates the third Rollover Function

FAQ

* There are no frequently asked questions for Pretty Rollovers

Videos

This is a video of Pretty Rollovers in action. In this demonstration, I go through the examples in the download File. Music is 'Born a Rockstar (Instrumental) - NEFFEX' · view the Pretty Rollovers video on YouTube

Support

Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support (limited) for my Software includes

Responding to questions or problems regarding the Software and its features
Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include

Customization and installation services
Support for third party software or ANY kind of development whatsoever

Before seeking support

Make sure your question is a valid Software Issue and not a customization request
Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
Ensure that you access to the VBOM is allowed and that Macros can run in Excel
Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
...or Contact Mark Kubiszyn via our Facebook Page - remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary or via Messenger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Future Builds

Update: I have now found a method that no longer requires a Timer for firing for Selections following a hyperlink or a Rollover! I will be releasing this method with this Project for the next release

Bug Fixes

* There are currently no bugs identified

Limitations

* You cannot have different Font Colours for different Buttons because they use the Hyperlink and Followed Hyperlink Styles but you can have different Font Colours for each set of Buttons on a Sheet

* Using Spaced Buttons, the Non-contiguous Ranges passed must be passed as a String (I have tried parenthasis to pass these disparate Ranges to no avail) so for this reason the Formula becomes 'Hard-coded'. Not really an issue as you can just Copy and Paste the Ranges, but it needs to be stated. For Contiguous Ranges of Buttons this is not an issue as the Rollover Functions use Ranges ie. $B15:$F15 or B$15:F$15

* Locking Drawing Objects yields some strange behaviour in Version 1 (beta testing), however I am going to test this behaviour with Version 2 and will feedback here with the results!

Changelog

You can find the version history in the Code Module for any Macro-enabled Software or read more information on the status of each release
- the latest Version including a description of any changes made is always shown first


12.10.2022 - (Version 2)

General release of Pretty Rollovers


04.09.2022 - (Version 1)

Never released - Beta test and development only