Introduction

Introducing Hyperlink Rollovers

Hyperlink Rollovers - Hyperlink Rollovers for Excel

This Software will combine some of my oldest and very latest (and most awesome) Rollover techniques to give you various options when using the Hyperlink Rollover in your future Projects. I will examine the basic Rollover and then take you on a journey through some pretty crazy methods of using the Rollover to do, well, just about anything. I have discovered new ways to use both a proper Hyperlink Tooltip together with a Hyperlink Rollover. I have discovered ways to navigate anywhere including selecting other Worksheets within the Workbook for a specific Range whenever a Hyperlink Rollover Cell is selected by the Mouse without ever capturing the Mouse click itself or using any of the techniques out there in the public domain

All of my investigations into Rollovers and my alternative techniques focus entirely on Cell Rollovers not Chart Rollovers and do not include Shapes or ActiveX Labels. This is a deep-dive into the Hyperlink Rollover subject, which I hope both newbies and old-hands alike will find interesting and useful as a complete terms of reference

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

If you like this Project you may also enjoy

Pretty Buttons · Pretty Rollovers · Dark Excel
xlui ·

Prerequisites

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

Features

A nifty Excel Workbook with an awesome background and helpful links/advice together with lots of Sheets of lovely Rollovers!
An insight or debrief on the Basic Rollover technique used when a Mouse is 'rolled' over a Cell or clicks into the Cell
A Hyperlink Rollover formula method to navigate anywhere including selecting other Worksheets within the Workbook for a specific Range whenever a Hyperlink Rollover Cell is selected by the Mouse without ever capturing the Mouse click itself, nice ;)
An examination of the different Return Types that you can use with the Hyperlink Rollover or in your own Functions ie. Range, String
How we can switch on the default (ugly) Tooltip for a Hyperlink Rollover
A method so that we can have both a proper Hyperlink Tooltip and use a Rollover without using any API
A Tricks Sheet featuring various things that Rollovers can do but you may never have known about, for example, Running more than 1 Function at the same time using a Hyperlink Rollover or daisy-chaining or Nesting the =HYPERLINK() Functions together
Rollovers without using ActiveX Labels or Shapes of any kind!
Create ultra-fast, smooth, Rollover Cell Buttons that highlight and de-highlight with a single Rollover Function and a Timer
My Inverse Intersect Method to highlight and de-highlight Ranges of Contiguous or Disparate Cells. THis method swpas out the Rollover Formula for a real Cell Hyperlink so that you get a Proper Toolip, can capture Mouse clicks and run any VBA Code
My Adaptive XY Method. This method came about as I personally do not like how the Hyperlink Rollover fires continuously for no reason. I mean once we roll the Mouse and we are on the Cell in my mind, we have done our work. I demonstrate 8 types of Rollover example including a Menu where you can Highlight a Cell within a Range and leave 1 Cell selected in a different Colour
Ways of tracking the Cell that is being Rolled over by outputting an Index as an Offset() for each Cell or as one Tracking Number to a specific Cell
Different ways of Jumping or Navigating to Cells and / or Worksheets
Methods of Adding Hyperlinks to Cells with Anchors, Tooltip and Text to Display to Cells using VBA with or without an Address to link to
A Timer() Method, where Cells are de-highlighted automatically as you move away from them using a degrading Timer via a Timer() API and the GetCursorPos() API with a TimerProc() Callback Subroutine

Pretty Rollovers · Well, hello there you cute little Rollover Tooltip!

Installation

Open the HyperlinkRollovers.xlsm File, test out all of the Examples to see how they work and take a look at the VBA Code behind all of them. Please read all of the info on any Sheet in the Workbook and in this online documentation before you begin to edit and create your own Hyperlink Rollovers or before turning to Support

Usage

HyperlinkRollovers.xlsm

Test out all of the Demos
Before you start editing and trying to create your own Hyperlink Rollovers, take some time to look at all of the Examples in all of the Sheets. All of the Sheets contain some links to licensing, a link to the nifty background used by Hyperlink Rollovers (Checkerboard by: Myriam Lefebvre) and the online Help documentation plus some words of wisdom and advice

The 'Basic Rollover' Sheet
The 'Basic Rollover' Sheet gives a real beginners insight into the Rollover technique. I touch briefly in my examples on what the Rollover technique can and cannot do. I discuss some best approaches to using it and a conclusion, where I make a little summary on the learnings so far. I also demonstrate how to use the Basic Rollover technique to capture Mouse clicks and run any Task you like afterwards. All of the Rollover Code is in the 'mdBasicRollover' Code Module with some Code also in the Worksheet Code Module for the Sheet. These Hyperlink Cell Rollovers all update a random number to Cell "O5". These Rollover Functions are Implicit, meaning the Functions themselves do not return a defined Variable Type and they do not allow a Tooltip to be displayed. Here is the 'Basic Rollover' Sheet:

The 'Basic Rollover' Sheet where I outline what the Rollover technique can and cannot do. I discuss some best approaches to using it and a conclusion, where I make a little summary on the learnings so far

The 'Basic Rollover' Sheet where I outline what the Rollover technique can and cannot do. I discuss some best approaches to using it and a conclusion, where I make a little summary on the learnings so far

For anyone not yet enlightened then here is a basic Rollover Function used in the Cell together with this Rollover Formula [=IFERROR(HYPERLINK(BasicRollover1(),"Rollme!"),"Rollme!")] without the brackets!:

' // Implicit Rollover Functions that do not return a defined variable type

Public Function BasicRollover1()
 Sheet1.Range("O5").Value2 = Rnd(999) * 10
End Function

Tips:
- Always use Center and Wrap Text formatting on Rollover Cells to ensure the whole Cell is covered by the Rollover as the Mouse is moved across
- Use the Friendly part of the Hyperlink Function and an IFERROR() Function combined with a Custom Format for the Text of the Rollover Cell for unbreakable Rollovers

Conclusions/Observations:
- You don't have to use the Hyperlink Style for Hyperlinks and Followed Hyperlinks
- The methods above are not perfect for capturing Mouse clicks on Rollover Cells. We will examine alternatives later on which address and solve this issue, giving you more choice of how you capture and deal with Mouse clicks
- You can run VBA Code using the Basic Rollover technique and the Selection.Address but you are limited in what you can achieve. A workaround is to use an API Time Thread
- You can run VBA Code using the Basic Rollover technique and the Worksheet_SelectionChange event but you are limited in what you can achieve. A workaround is to use an API Time Thread
- If you are already using the Basic Rollover technique in some form or other, then adopting/integrating my Timer Thread technique will help you run any kind of VBA Code

Misc Information:
- When using two Functions a RollOn and a RollOff you may get little triangles appear for inconsistent Formula - hover near these and choose ignore error to switch them off

The 'Examining the Return Value' Sheet
The 'Examining the Return Value' is a bit of an experiment into the return types in our Functions that can be used with the Hyperlink Rollover Function and if or how we can set and use these to influence and ultimately improve our Rollovers performance and flexibility. The default =HYPERLINK() return type will be a Range or a String. I examine changing the return value to a String which gives us the ability to still use the Rollover to update a random number to the Sheet (this could also be the Cell Colour etc.) and to also navigate to where we want following a Mouse click, which incidently does not require us to explicitly capture the Mouse click using this methodology. I also look at turning on the default 'ugly' Tooltip and at highlighting and de-highlighting Cells containing Rollovers

For those of you wondering, one of my simple Rollover techniques used to navigate to a Cell can be shown by this Rollover Function and this Rollover Formula [=IFERROR(HYPERLINK(ReturnValueRollover3(),"Roll me!"),"Roll me!")] without the brackets! Oh and if you want to see more methods, including using a Parameter than please purchase this Software for the total solution ;)

' if we set the return type of the Hyperlink Rollover Function to String we can return the Cell that we want to navigate to
' this will allow us to change Cell Colours for Rollovers and to navigate somewhere
Public Function ReturnValueRollover3() As String
 Sheet2.Range("O5").Value2 = Rnd(999) * 10
 ReturnValueRollover3 = "#O5"
End Function

We can use two Functions to Highlight and De-highlight a selection of similar and / or different or disparate Cells. A typical RollOn() Formula would look something like this [=IFERROR(HYPERLINK(ReturnValueRollOn2(F40,G40:J40),"Roll me!"),"Roll me!")] without the brackets! And a typical RollOff Formula like this [=IFERROR(HYPERLINK(ReturnValueRollOff2($F$40:$J$40)),"")] without the brackets! The Code to run these would look something like this:

' two Functions to highlight and de-highlight disparate Ranges
Public Function ReturnValueRollOn2(ByVal RolloverCell As Range, ByVal RolloverRange As Range) As String
 Sheet2.Range("O5").Value2 = Rnd(999) * 10
 RolloverRange.Interior.Color = 16514043
 RolloverCell.Interior.Color = 12373698
 ReturnValueRollOn2 = "#" & RolloverCell.Address
 If Selection.Address = RolloverCell.Address Then
    MsgBox "Foobar"
    RolloverCell.Interior.Color = 16514043
    ' won't work - use the Worksheet_SelectionChange event handler or an API!
    'Sheets.Add
 End If
End Function

Public Function ReturnValueRollOff2(ByVal RolloverRange As Range)
 RolloverRange.Interior.Color = 16514043
End Function

The 'Examining the Return Value' Sheet where I look at how we set the return type for both the =HYPERLINK() and a Rollover Function. I discuss some best approaches and a conclusion, where I make a little summary on the learnings so far

Tips:
- Always use Center and Wrap Text formatting on Rollover Cells to ensure the whole Cell is covered by the Rollover as the Mouse is moved across
- Use the Friendly part of the Hyperlink Function and an IFERROR() Function combined with a Custom Format for the Text of the Rollover Cell for unbreakable Rollovers

Conclusions/Observations:
- By default we expect the =HYPERLINK() Function to return a Range, so we need to set a Range in our Functions. However we can return a Variant or String which contains a Link Location ie. "#O5"
- If we set the return type of the Hyperlink Rollover Function to Range then we need to return a Range Object otherwise the Function will give an error: cannot open the specified file
- If we set the return type of the Hyperlink Rollover Function to String we can return the Cell that we want to navigate to. This will allow us to change Cell Colours for Rollovers and to navigate somewhere
- Incidentally if we use the return type of the Hyperlink Rollover Function as a String, we do not get an error if we do not return anything
- And it follows that by setting this Cell using a Parameter it gives us more control over navigation, where we can even select other Sheets and we don't need to capture the Mouse click
- We can turn on the default (ugly) Tooltip, however, whenever we run Code other than simply returning a navigation Cell from the Hyperlink Rollover Function the Tooltip is blocked unless a Range is returned to the Cell itself
- We can use this technique to Highlight single Cells or Rows or Columns of Cells. The individual Rollovers need to be configured for the Disparate Ranges in order to switch Rollover Cells On/Off
Again, we can use the Selection.Address technique to capture a Mouse click and an API to run any Code that we want

Misc Information:
- When using two Functions a RollOn and a RollOff you may get little triangles appear for inconsistent Formula - hover near these and choose ignore error to switch them off

The 'Combining Rollovers' Sheet
In this Sheet we look at attempts to combine a Cell Hyperlink or any Text with a proper Tooltip and a Hyperlink as a Rollover. It's kind of not possible to run both as the Hyperlink added to a Cell using VBA Code will always get precedent over the =HYPERLINK() Function but we look at doing both anyhow with some examples with the last example only using a linked Hyperlink and the simple Text in a Cell of 'Aardvark'. I look at linking a Macro to a Cell Hyperlink and capturing the Mouse click, which can be made repeatedly on a Cell. When making a Mouse click on a Hyperlinked Cell the Macro Code is ran and you can see the random number update, but only once, per Mouse click. I then look at utilising the return value of the Rollover Function by linking a Hyperlink to a Macro to navigate to a Cell using [Set CombiningHyperlinksRollover2 = Range("O5")] in the Rollover Function in the VBA Code, or navigating to another Sheet using [Set CombiningHyperlinksRollover3 = Sheet1.Range("F6")]. I also use the Worksheet_SelectionChange event handler to capture a Mouse click and Insert a Sheet. You can add a Cell Rollover using Code like this where "#CombiningHyperlinksRollover1()" is the Name of the Macro / Function that you want to link:

Sub AddCellHyperlink1()
   ActiveCell.Hyperlinks.Add ActiveCell, "", "#CombiningHyperlinksRollover1()", "Well, hello there you cute little Rollover Tooltip!", ""
End Sub

The 'Combining Rollovers' Sheet where I attempt to combine a Cell Hyperlink or any Text with a proper Tooltip and a Hyperlink as a Rollover

Tips:
- Always use Center and Wrap Text formatting on Rollover Cells to ensure the whole Cell is covered by the Rollover as the Mouse is moved across
- Use the Friendly part of the Hyperlink Function and an IFERROR() Function combined with a Custom Format for the Text of the Rollover Cell for unbreakable Rollovers

Conclusions/Observations:
- We can attempt to add both a Cell Hyperlink for the proper Tooltip together with a Hyperlink Rollover to create a Cell that when clicked can run some Code or navigate to a Range and / or Sheet
- We can use the Worksheet_SelectionChange event handler to capture a Mouse click and then run any Code without an API
- Unfortunately, you cannot, for example update a Cell or highlight a Cell as the Hyperlink takes precedent over the Cell Hyperlink Rollover, therefore it is not possible for both Hyperlink and a =HYPERLINK() Function to work together to both highlight a Cell and have a nice Tooltip

The 'Tricks' Sheet
This Sheet looks at some weird little tricks you can do using the Cell Rollover technique with the =HYPERLINK() Function. First of all I look at running more than one Function at a time by passing one or more Functions wrapped in parenthesis, to output two random numbers to two Cells using two Rollover Functions, with a Formula like this (the last reference "#F12" allows the Rollover Functions to have the Cell clicked by a Mouse without anything happening). The only caveat is that we have to pass the Cell Address as the IFERROR() part and then use a Custom Format on the Cell as [;;;"Roll me!"] otherwise we get #VALUE!:

=IFERROR(HYPERLINK((Trick1(),Trick2())),"#F12")

The next trick I look at is using a Rollover Function to create a Bar using two Ranges as separate Parameters, that can be highlighted and set at one of three positions, Low, Medium or High. I repeat this with a coloured Bar by adding an additional Parameter to the Rollover Function. After this, I then show you another method of running multiple Rollover Functions. I call it Daisy-chaining or Nesting the =HYPERLINK() Functions. My examples show both how as you move over the Rollover Cell and pause the Functions also pause and how you can make them continously fire without stopping. As you continually Rollover a Time in seconds will be updated in the Sheet so that you can examine the effect. To Daisy-chain a Rollover Function, you can use a Formula like this (I use the IFERROR() Function to output the "Roll me!" Text and make subtle changes to each Rollover Function to allow the pause when hovering with the Mouse or continous firing):

=IFERROR(HYPERLINK(Trick5(),HYPERLINK(Trick6(),HYPERLINK(Trick7()))),"Roll me!")

The 'Tricks' Sheet where I look at some weird little tricks you can do using the Cell Rollover technique with the =HYPERLINK() Function like Daisy-chaining or Nesting the =HYPERLINK() Function

Tips:
- Always use Center and Wrap Text formatting on Rollover Cells to ensure the whole Cell is covered by the Rollover as the Mouse is moved across
- Use the Friendly part of the Hyperlink Function and an IFERROR() Function combined with a Custom Format for the Text of the Rollover Cell for unbreakable Rollovers
- using this technique may require you to also use a Custom Cell Format like this [;;;"Low"]

Conclusions/Observations:
- You can run multiple Rollover Functions and navigate to a Cell either by grouping and then wrapping them in parenthesis or by daisy-chaining or Nesting the =HYPERLINK() Function
- You can create a Bar using two Ranges and use Colour to highlight it
- When using either the Daisy-chain (Nested) technique or the Functions wrapped in parenthesis trick, that we don't have to move when we are over the Cell to continuously fire the Rollover and Code!

The 'My Adaptive XY Method' Sheet
I created this method because I was sick of the fact that the rollover was just a continuous firing of the Function and it annoyed me that whenever you hovered over a Cell with the Mouse that it did not stop. My solution was to adopt an XY approach, meaning a Column and Row number would be used as an Index to identify the Cell that was being rolled over, writing this out to a Named Range. If the Formula recognised that you were in fact over the Cell, then it would not run the Rollover Function anymore, simple! So this is my adaptive XY method, many years old now, but still sound and works well with any Rollovers as this Sheet will testify. Check out all of my Demos on this Sheet, there are 8 from a very basic Rollover using the technique through to a Menu where you can highlight and select a specific Cell which is also highlighted differently to capturing Mouse clicks in Merged Cell Ranges and a Table Row Rollover. As it is so versatile, two typical Rollover Function Formula using this technique would be Named and written something like this (Please note: when you use the Hyperlink Rollover Adaptive Method it requires the '@' Implicit Intersection Operator):

' RollOn or RollOver
=IF(MouseoverXY=INT(COLUMN(G20)&0&ROW(G20)),"",IFERROR(HYPERLINK(@AdaptiveRollover1(MouseoverXY,COLUMN(G20)&0&ROW(G20))),"Rollover"))

' RollOff or RollOut
=IF(MouseoverXY=INT(0),"",IFERROR(HYPERLINK(AdaptiveRollover1(MouseoverXY,"0")),""))

The MouseoverXY is a Named Range. I include 7 Named Ranges for the Demos. A Named Range contains the current Cell coordinates ie. Column Number + 0 + Row Number, so for A Rollover of Cell "G35" the Named Range would equal '7035'. They are named 'MouseoverXY', 'MouseoverXY1', 'MouseoverXY2' etc. Alongside the Rollover Function, each Rollover requires a Conditional Format to accomplish the Cell highlight

Steps to create Rollovers in your own Projects
1. add the following UDF's (User Defined Functions) to a new or existing Code Module (not a Worksheet Code Module)
Public Function Rollover(MyIndex As String): [MouseoverXY] = MyIndex: End Function
Public Function Reset(): [MouseoverXY] = 0: End Function
2. add a Defined Name MouseoverXY to any Cell in your Worksheet
3. add the Formula below to any Cell changing all occurances of "B10" to the new Cell reference
=IF(INT(COLUMN(B11)&0&ROW(B11))=MouseoverXY,"",IFERROR(HYPERLINK(Rollover(COLUMN(B11)&0&ROW(B11))),"Rollover"))
4. in the same Cell add a Conditional Format with the Formula below changing all occurances of "B10" to the new Cell reference. Mouse your Mouse away & back over the Rollover
=INT(COLUMN(B11)&0&ROW(B11))=MouseoverXY

Additional Steps to enhance your Rollovers
5. to ensure that the Mouseover event triggers for the entire width & height of the Cell, right-click the Rollover Cell, select 'Format Cells…'. On the 'Alignment' Tab tick the 'Wrap text' Checkbox
6. to create multiple Rollovers or expand the Rollover into a grid, select the Rollover Cell by the little handle and drag it across X Columns, down Y Rows
7. to move the Rollover, select the Rollover Cell and drag it anywhere in your Worksheet (or move using CTRL+X & then press Enter or CTRL+V)
8. to copy the Rollover to a new location, select the Rollover Cell and then Copy & Paste it anywhere in your Workbook (or copy using CTRL+C & then press Enter or CTRL+V)
- Please note: when you copy a Rollover Excel will create a separate Conditional Format for the new Cell or Range
9. to give the Rollover display Text, right-click on the Rollover Cell, select 'Format Cells…'. On the 'Number' Tab select 'Custom' & enter ;;;"Hello World" ("Hello World" will be the new Text displayed for your Rollover)
10. to clear the Rollover when your Mouse leaves the Cell, add the Formula below to any of the Rollover adjacent Cells to set the MouseoverXY Defined Name to 0 (zero
) =IF(MouseoverXY=0,"",IFERROR(HYPERLINK(Reset()),""))

The 'My Adaptive XY Method' Sheet. I created this method because I was sick of the fact that the rollover was just a continuous firing of the Function and it annoyed me that whenever you hovered over a Cell with the Mouse that it did not stop

The 'My Adaptive XY Method' Sheet. I created this method because I was sick of the fact that the rollover was just a continuous firing of the Function and it annoyed me that whenever you hovered over a Cell with the Mouse that it did not stop

The 'My Inverse Intersect Method' Sheet
Okay so this is my favourite Sheets and techniques. The reason I like this technique is that it is very versatile, robust and delivers a Rollover Function() using the =HYPERLINK() Function and a Cell Hyperlink with a Proper Tooltip, yep, you got it, just like your Mama used to make! It's also the technique I deploy for my Pretty Rollovers Project - some of the Code may be slightly different, but the basic premise and process are the same

A Typical pairing of Contiguous Rollover Formula Functions will look something like this (pass Rollover Cell, Rollover Range, a Tooltip, a Macro Name, Rollover Range Colour, Rollover Cell Colour):

' RollOn
=IFERROR(HYPERLINK(ContiguousRollOn(G22,$G22:$K22,"I am a proper Tooltip","Macro1",8364426,11847866),"Roll me!"),"Roll me!")

' RollOff
=IFERROR(HYPERLINK(ContiguousRollOff($G$22:$K$22,8364426),""),"")

And a typical pair of Disparate Rollover Formula Functions will look something like this (pass Rollover Cell, Rollover Range, a Tooltip, a Macro Name, Rollover Range Colour, Rollover Cell Colour):

' RollOn
=IFERROR(HYPERLINK(DisparateRollOn("G32,I32,K32,M32,O32",G32,"This is a proper Tooltip","Macro2",8364426,11847866),"Roll me!"),"Roll me!")

' RollOff
=IFERROR(HYPERLINK(DisparateRollOff("G32,I32,K32,M32,O32",8364426)),"")

There are subtle differences to both sets of actual Rollover Functions, the Disparate Function has to pass the Ranges as Cells and Ranges as Strings because using wrapping the Ranges in Parenthesis () did not work. I will not go into the Functions here other than to say they are pretty cool and you will need to purchase this Software for the total solution!

The 'My Inverse Intersect Method' Sheet. My favourite Sheet and technique. The reason I like this technique is that it is very versatile, robust and delivers a Rollover Function() using the =HYPERLINK() Function and a Cell Hyperlink with a Proper Tooltip, yep, you got it, just like your Mama used to make!

The 'My Inverse Intersect Method' Sheet. My favourite Sheet and technique. The reason I like this technique is that it is very versatile, robust and delivers a Rollover Function() using the =HYPERLINK() Function and a Cell Hyperlink with a Proper Tooltip, yep, you got it, just like your Mama used to make!

Tips:
- Always use Center and Wrap Text formatting on Rollover Cells to ensure the whole Cell is covered by the Rollover as the Mouse is moved across
- Use the Friendly part of the Hyperlink Function and an IFERROR() Function combined with a Custom Format for the Text of the Rollover Cell for unbreakable Rollovers

Conclusions/Observations:
- My Inverse Intersect Hyperlink Rollover Method enables us to pass in a Range which is reset for all Cells in the Range except for the Rolled Cell which is then highlighted in a different Colour
- The basic premise is that the RollOn Function will add a Hyperlink Tooltip to the Cell on the fly and then the RollOff Function will remove it again
- Will return the inverse of the intersecting Range ie. InverseIntersect(Range("J21:L21"), Range("K21")) will return Range("J21,"L21")
- There are two sets of Functions that can be used for Contiguous Cell Ranges or Disparate Cells
- Allows you to have a Proper Tooltip not the default 'ugly' one used by the =HYPERLINK() Function
- Adding an extra Index Parameter allows us to easily Track which Cell we are currently hovering on

Misc Information:
- When using two Functions a RollOn and a RollOff you may get little triangles appear for inconsistent Formula - hover near these and choose ignore error to switch them off
- When setting the Hyperlinks and Followed Hyperlinks for this technique for a Project the changed Hyperlinks should persist, so you would not need to update the Sheet, ergo, you would not notice them change

The 'Adding Hyperlinks using VBA' Sheet
This Sheet will demonstrate to you, adding Cell Hyperlinks with Anchors, Tooltip, Text to Display and Linking to Macros using VBA. There are 2 examples, the first example made up of two examples with a variation - a simple example just doing nothing and an example to do the same but also to navigate to a Cell in the current Sheet (notice the slight change in the Code ie. Address:="#H17"):

ActiveSheet.Hyperlinks.Add Anchor:=Range("F13"), Address:="", TextToDisplay:=CStr("Link Text"), ScreenTip:="Tooltip Text"

ActiveSheet.Hyperlinks.Add Anchor:=Range("F17"), Address:="#H17", TextToDisplay:=CStr("Link Text"), ScreenTip:="Tooltip Text"

The second example demonstrates how you can link up the Hyperlink to Code to run a Macro. Again if you were to combine this with a Timer() you can do just about anything (notice now that we change the Address part to: Address:="#HyperlinkLinkMacro1()", which contains our Macro Name that we want to link to, together with the '#' (hash) prefix):

ActiveSheet.Hyperlinks.Add Anchor:=Range("F21"), Address:="#HyperlinkLinkMacro1()", TextToDisplay:=CStr("Link Text"), ScreenTip:="Tooltip Text"

The 'Adding Hyperlinks using VBA' Sheet. Some simple examples of adding Hyperlinks using VBA Code

Tips:
- Always use Center and Wrap Text formatting on Rollover Cells to ensure the whole Cell is covered by the Rollover as the Mouse is moved across
- Use the Friendly part of the Hyperlink Function and an IFERROR() Function combined with a Custom Format for the Text of the Rollover Cell for unbreakable Rollovers

Conclusions/Observations:
- You can add Hyperlinks to Cells with Anchors, Tooltip and Text to Display to Cells using VBA with or without an Address to link to
- You can also link your Hyperlinks up to Macros
- You can have a Proper Tooltip, not one of those 'ugly' =HYPERLINK() ones

The 'Timer Rollover Method' Sheet
This Sheet demonstrates how you can use a Timer to do everything from a single Rollover Function. Buttons 1, 2 and "Click me!" are clickable by the Mouse. The Code will not fire again if you are the Cell that you clicked, therefore it is preferable that, in your VBA Code you select another Cell or Sheet. There are 13 Buttons on this Sheet that allow you to hover over and click using the Mouse. All of the Buttons are highlighted as you hover over them - notice how silky-smooth and fast the technique is!

This method uses both the Timer() API and the GetCursorPos() API in combination, which also means that you Cells are de-highlighted automatically as you move away from them using a degrading Timer. This method uses a low-level, TimerProc() Callback Subroutine in conjunction with the Timer() and GetCursorPos() API's as per below (the [ActiveSheet.Name = "Timer Rollover Method" And] can be removed - I had to add it for this example Project, due to the number of different =HYPERLINKS() that fire on opening the Workbook, but it is not generally required):

'-¬ TimerProc, low-level timer callback Subroutine
Public Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
   On Error Resume Next
   If Ticker > 3 Then
      Dim CursorPoint As POINTAPI
      Dim RangeCell As Range
      GetCursorPos CursorPoint
      Set RangeCell = ActiveWindow.RangeFromPoint(CursorPoint.HorizontalPoint, CursorPoint.VerticalPoint)
      If Not RangeCell Is Nothing And RangeCell.Value2 = vbNullString Then
         If ActiveSheet.Name = "Timer Rollover Method" And RangeCell.Address <> LastCellAddress Then Range(LastCellAddress).Interior.Color = 12897152
         LastCellAddress = "A1"
      End If
      EndTimer
   End If
   Ticker = Ticker + 1
End Sub

The 'Timer Rollover Method' Sheet. Demonstrates how you can use a Timer() API and the GetCursorPos() API to do everything from a single Rollover Function

Tips:
- Always use Center and Wrap Text formatting on Rollover Cells to ensure the whole Cell is covered by the Rollover as the Mouse is moved across
- Use the Friendly part of the Hyperlink Function and an IFERROR() Function combined with a Custom Format for the Text of the Rollover Cell for unbreakable Rollovers

Conclusions/Observations:
- Very simple to set up, just add the Code in the 'mdTimerRollover' and Copy & Paste in the Button Cell in "F15". Drag the Button right and rename it to Button2 etc. and you are good to go
- The Rollover is extremely fast and smooth when used for Cell highlighting
- There is no requirement for additional Funtions ie. RollOff() as this is not needed for this technique, nice ;)
- You can just keep copying and Pasting your Buttons
- The Code will not fire again if you are the Cell that you clicked, so once you capture the Mouse click, you need to navigate away from the Cell
- You can combine this technique with the Timer Thread technique to run any VBA Code that you like
- Cells are de-highlighted automatically as you move away from them using a degrading Timer

Misc Information:
- A Double-click event handler is used here, Worksheet_BeforeDoubleClick. I use this to block edit in Cell data. it tidy's up the end-users experience when clicking links
- To prevent False Colouring of a Cell when the Workbook opens due to lots of updating Hyperlinks, you need to hard-code the Cheet Name into the VBA Code. If you were just using this in one Sheet in a Project this can be removed!

The 'Timer Colour Optional Params' Sheet
This Sheet is similar to the 'Timer Rollover Method' Sheet but has slightly different Code and allows you to pass 2 or 4 Colours into the Rollover() Functions to change the background Cell Fill, highlight Fill and Font Colours. Simply Copy & Paste these Rollovers anywhere in your Sheet. Add the Code in the 'mdTimerColourParams' Code Module and you are good to go. You can choose whether to include the Optional Font Colour and Rollover Font Colour

The 'Timer Colour Optional Params' Sheet. Similar to the 'Timer Rollover Method' Sheet but has slightly different Code and allows you to pass 2 or 4 Colours into the Rollover() Functions to change the background Cell Fill, highlight Fill and Font Colours

The 'Timer Colour Optional Params' Sheet. Similar to the 'Timer Rollover Method' Sheet but has slightly different Code and allows you to pass 2 or 4 Colours into the Rollover() Functions to change the background Cell Fill, highlight Fill and Font Colours

Screen Shots

The 'Basic Rollover' Sheet where I outline what the Rollover technique can and cannot do. I discuss some best approaches to using it and a conclusion, where I make a little summary on the learnings so far

The 'Examining the Return Value' Sheet where I look at how we set the return type for both the =HYPERLINK() and a Rollover Function. I discuss some best approaches and a conclusion, where I make a little summary on the learnings so far

The 'Combining Rollovers' Sheet where I attempt to combine a Cell Hyperlink or any Text with a proper Tooltip and a Hyperlink as a Rollover

The 'Tricks' Sheet where I look at some weird little tricks you can do using the Cell Rollover technique with the =HYPERLINK() Function like Daisy-chaining or Nesting the =HYPERLINK() Function

The 'My Adaptive XY Method' Sheet. I created this method because I was sick of the fact that the rollover was just a continuous firing of the Function and it annoyed me that whenever you hovered over a Cell with the Mouse that it did not stop

The 'My Inverse Intersect Method' Sheet. My favourite Sheet and technique. The reason I like this technique is that it is very versatile, robust and delivers a Rollover Function() using the =HYPERLINK() Function and a Cell Hyperlink with a Proper Tooltip, yep, you got it, just like your Mama used to make!

The 'Adding Hyperlinks using VBA' Sheet. Some simple examples of adding Hyperlinks using VBA Code

The 'Timer Rollover Method' Sheet. Demonstrates how you can use a Timer() API and the GetCursorPos() API to do everything from a single Rollover Function

The 'Timer Colour Optional Params' Sheet. Similar to the 'Timer Rollover Method' Sheet but has slightly different Code and allows you to pass 2 or 4 Colours into the Rollover() Functions to change the background Cell Fill, highlight Fill and Font Colours

FAQ

* There are no frequently asked questions for Hyperlink Rollovers

Videos

This is a video of the Hyperlink Rollovers in action. In this demonstration, I go through all of the examples in the download File. Music is 'I Just Wanna Be Great - NEFFEX' · view the Hyperlink Rollovers video on YouTube

Support

Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support for my Software

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
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. 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

* No ideas yet!

Bug Fixes

* There are currently no bugs identified

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


01.11.2022 - (Version 1)

General release of Hyperlink Rollovers