Hover with mouse cursor to change stock in a candlestick chart

This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above shows a stock chart and two company names in the top right corner.

Hover with the mouse cursor over a company name to trigger a macro. The macro checks which company and changes the chart data source accordingly.

The chart is instantly updated with the values corresponding to the chosen company (series name). The technique shown in this article can be applied to any kind of chart.

What you will learn in this article

• Create a hyperlink using the HYPERLINK function.
• Utilize a User defined Function (UDF) in the hyperlink formula.
• Explain how to change chart series programmatically.
• How to change axis min and max values programmatically.
• Where to put UDF code in a module of your workbook.
• Save the workbook with the correct file extension.

Hover over a company name and the chart instantly changes data source.

Create a hyperlink

To insert a hyperlink you have two options, manually add a hyperlink or use the HYPERLINK function.  In this example, I will use the HYPERLINK function to create a hyperlink.

Formula in cell J2:

Formula in cell J3:

I will explain these formulas below.

Explaining formula in cell J2

I recommend that you use the "Evaluate Formula" tool to understand formula calculations in greater detail. Select a cell containing the formula you want to examine.

Go to tab "Formulas" on the ribbon. Press with left mouse button on "Evaluate Formula" button and a dialog box appear, see image above. Press with left mouse button on the "Evaluate" button to iterate through each step in the formula calculation, press with left mouse button on "Close" button to dismiss the dialog box.

Step 1 - HYPERLINK function

The HYPERLINK function has two arguments: HYPERLINK(link_location, [friendly_name])

Based on the docs the link_location argument can be the path to a file, file on a server, file on the internet, a workbook, webpage or a bookmark in a word document. However, it is also possible to use a User Defined Function that is rund when you hover over the link. This is not mentioned in the documentation.

The [friendly_name] argument is optional, we are going to use nothing "". If we type something here it will be formatted as a hyperlink in the worksheet which is not what we want. Step 3 below will explain how to show a text string without being formatted as a hyperlink.

HYPERLINK(MouseHover("Google"),"")

Step 2 - Prevent errors

The IFERROR function returns a value you specify if the first argument returns an error. This will prevent an error appearing if something goes wrong with the HYPERLINK function or the UDF.

IFERROR(HYPERLINK(MouseHover("Google"),""),"")

Step 3 - Append text

The ampersand character allows you to concatenate two or more strings. This will make sure that a value is shown in the cell. The hover will work anyway with an empty cell, however, it will certainly confuse the user when the UDF is rund perhaps by accident.

"Google"&IFERROR(HYPERLINK(MouseHover("Google"),""),"")

User defined function

'Name user defined function, dimension argument variable and declare it's data type
Function MouseHover(str As String)

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveSheet.ChartObjects("Chart 2")

    'Check if variable str meets condition and value in cell A1 in sheet1 is not equal to variable str
    If str = "Google" And Sheet1.Range("A1") <> str Then

        'Save text string "Google" to cell A1
        Sheet1.Range("A1") = "Google"

        'Change chart source to "A2:A54,C2:F54" in worksheet name specified in cell A1
        .Chart.SetSourceData Source:=Sheets("" & Range("A1") & "").Range("A2:A54,C2:F54")

        'Change axis max value based on number in cell K2
        .Chart.Axes(xlValue).MaximumScale = Range("K2")

        'Change axis min value based on number in cell L2
        .Chart.Axes(xlValue).MinimumScale = Range("L2")
 
    'Check if variable str equals "Apple" and cell A1 is not equal to variable str
    ElseIf str = "Apple" And Sheet1.Range("A1") <> str Then

        'Change cell contents in cell A1
        Sheet1.Range("A1") = "Apple"

         'Change chart source to "A2:A54,C2:F54" in worksheet name specified in cell A1
        .Chart.SetSourceData Source:=Sheets("" & Range("A1") & "").Range("A2:A54,C2:F54")

        'Change axis max value based on number in cell K3
        .Chart.Axes(xlValue).MaximumScale = Range("K3")

        'Change axis min value based on number in cell L3
        .Chart.Axes(xlValue).MinimumScale = Range("L3")
    End If
End With
End Function

Where to put the code?

1. Press Alt + F11 to open the Visual Basic Editor.
2. Select your workbook in the Project Explorer.
3. Press with left mouse button on "Insert" on the menu.
4. Press with left mouse button on "Module" to create a module in your workbook.
5. Copy above VBA code.
6. Paste to code module, see image above.

Calculate max and min values for chart axis

The formulas below returns the min and max values for each chart source using the min and max values in cell range D2:D54.

Formula in cell K2:

Formula in cell K3:

Formula in cell L2:

Formula in cell L3:

Get the Excel file

Interactive-candlestick-chart.xlsm

Built-in Charts

Combo Charts

Chart elements

Chart basics

Custom charts

Sparklines

Highlight chart elements

Interactive charts

Animate

Advanced charts

Stock charts

Excel categories

Comments (3)