Excel VBA Case Study #2 – Map Mouseover

[IMAGE] Interactive Excel Map with Mouse Hover Effect

Summary:  Have you ever seen an amazing Excel spreadsheet and wondered how to make it yourself? In this article, I’ll show you a special effect to use in Excel dashboards with maps or other shapes.

We’ll build a country map that “knows” where your mouse cursor is. It highlights the part of the map that’s under your mouse while showing the name of the corresponding state. Unlike other methods there’s ZERO clicking.

Sound like a challenge? Don’t worry I’ll walk you through step-by-step. We can often “do the impossible” in Excel with a bit of VBA programming and creativity. Let’s get started with today’s guest post by Chris Cox…

Difficulty: Advanced

Download the Sample Workbook

[Image] Download Workbook Download the sample file with VBA code
Interactive-Map-Hover-Mouse.xlsm (270 Kb)

#1 – What makes this workbook special?

» Back to contents

Sometimes the best ideas come out of necessity. Kamal Bharakhda is a VBA developer and report automation specialist who works with Launch Excel. He recently helped a client who needed an interactive map of Algeria in Excel.

You can see the result in this animated GIF (no mouse clicking involved!)

The map shows different data depending on where the user hovers the mouse cursor. The user does not need to click on the map itself.

You can download the dashboard workbook file here.

Are you WOWed?! Got ideas for using this?

Are you wowed that Excel can “know” where your mouse cursor is? Do you have a great idea on how you’d impress your boss with this? Make sure you leave a comment below to share how you would use it.          

Here’s an incentive for commenting… If your idea is exciting enough we’ll work with you to make it into a full tutorial and template that’s 100% FREE for you. But you must leave a comment below to qualify! And explain how this would benefit you.

Back to Kamal

OK back to Kamal… Although he never created anything like this, he was confident in his VBA skills. He was also sure he could find the building blocks online.

He first needed an Excel-compatible map. After a bit of research, he learned that the map needed to be in the Windows Metafile (WMF) format. He found such a map at https://d-maps.com/.

He knew he’d be able to name each area of the map, and he could use VBA code to perform certain actions based on the area over which a user hovered. He also knew that he could use the Excel VLOOKUP formula to find data for each area from another sheet in his workbook.

With the results of his research in hand, he set upon his quest to exceed his customer’s expectations by using Excel + VBA + a little creativity!

#2 – How to set up the workbook?

» Back to contents

Kamal first had to download the map of Algeria. He browsed the d-maps site until he found a map that looked just right – here’s an example of one that could work: https://d-maps.com/carte.php?num_car=34295&lang=en. He then downloaded the map in the WMF format by clicking on the “WMF” icon.

Once he saved the file to his computer, he inserted it into the Excel workbook as a picture.

To refer to each state individually he ungrouped the states. To do so, he right-clicked on the map, selected “Grouping,” and clicked “Ungroup.”

After he ungrouped he had to find a way to reference each Algerian state using VBA. He decided to assign a code to each state.

He first viewed the map in design mode by clicking the “Design Mode” button on the “Developer” tab.

While in design mode, he clicked each state individually and typed a number representing that state in the name box.

He then created a “DATABASE” sheet in which he built a reference table with the number of each state (=”State Index”) and the State Names.

After he finished this he was ready to start the most critical part – writing the VBA code. This is where the magic happens! 🙂

#3 – How to code the VBA to work on mouseover?

» Back to contents

Although the steps in section two set up the basic workbook, hovering over the map itself isn’t an event that Excel recognizes. Before writing the code, Kamal needed one more piece to allow the VBA to manipulate the map.

VBA recognizes when users take certain actions – such as mouseover – related to controls (e.g., command button, combo box, check box). It doesn’t, however, recognize mouseover related to random shapes. So Kamal used label controls together with mouseover. Let’s walk through the steps.

He placed one or more label controls over each Algerian state, but he didn’t type any text in the labels. This made the labels invisible to the user. However, they were very useful to the programmer!

As you can see, he used four labels to cover the state in the above screenshot.

He then set up the VBA code to run on the “MouseMove” event, which means that it will run each time a mouse hovers over the object referenced in the code.

Private Sub L1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
    Call ChangeColor(1)
End Sub

In the snippet above, the code triggers when a user moves his or her mouse cursor over the label named “L1”. Whenever that occurs, the program calls the “ChangeColor” subroutine and passes a value (1 in this example) identifying the state to that sub.

#4 – Explaining the rest of the code

» Back to contents

Now that we’ve reviewed how to set up the code to run on mouseover, let’s look at the subroutine that the MouseMove event triggers. Here’s the full macro:

Sub ChangeColor(ByRef ControlInt As Integer)
    On Error Resume Next

    For Each Shape In Sheet1.Shapes
        Shape.Fill.ForeColor.RGB = RGB(255, 255, 255)
    Next Shape

    With Sheets("MAP")
        .Shapes(ControlInt).Fill.ForeColor.RGB = RGB(255, 255, 0)
        .Range("L1").Value = ControlInt
    End With
    
End Sub

The first part of the “ChangeColor” Sub looks like this:

Sub ChangeColor(ByRef ControlInt As Integer)
    On Error Resume Next

After declaring the sub, the “On Error Resume Next” line simply tells the code to keep running even if it hits an error.

Next, the code loops through every shape in the workbook’s first sheet (“MAP”) and set’s the fill color for that shape:

    For Each Shape In Sheet1.Shapes
        Shape.Fill.ForeColor.RGB = RGB(255, 255, 255)
    Next Shape

In this sheet, the individual states making up the Algerian map are each their own shape. Therefore, this code is setting the color of every state in the map. Color “RGB(255, 255, 255)” represents the color white. So, at the end of this snippet, all states will be white.

The next four lines of code run the actions that pull everything together!

    With Sheets("MAP")
        .Shapes(ControlInt).Fill.ForeColor.RGB = RGB(255, 255, 0)
        .Range("L1").Value = ControlInt
    End With
End Sub

For the “MAP” sheet, the second line of code sets the color of correct state. The VBA knows which state to color based on the identifying number passed to this sub from the prior sub. The macro stores that value in the “ControlInt” variable.

To make sure it’s easy for the user to know which state he or she is hovering over, the VBA uses color code “RGB(255, 255, 0)” to change it from white to yellow.

The next line of code sets the value of cell “L1” to the state’s code. This triggers the final action on the worksheet.

This action uses a standard Excel VLOOKUP function rather than more VBA:

=VLOOKUP($L$1,DATABASE!$A$1:$B$49,2,FALSE

This VLOOKUP, which is in cell “M9”, uses the value of cell “L1” to find the corresponding state in the “DATABASE” sheet. Once it finds the state, it grabs the state’s name from column B and displays it in “M9”.

As you can see from the above, creating an Excel mouseover map is easy when you know how. With just a few lines of code, what once seemed unthinkable becomes reality!

#5 – Other ways to use mouseover

» Back to contents

In this article we’ve walked through the basics of an Excel mouseover map. As you might imagine, though, this functionality can be used to solve much more complex business problems.

In our workbook, you could use the value in “L1” to pull detailed data about the yellow state into a table:

In this case, the data comes from a separate sheet that includes a series of formulas that use the value of “L1” to pull state-specific data.

You could then use the values in the table to create a variety of charts:

In addition, you could add transparent labels over images other than maps, and then trigger any action you want based on the “MouseMove” event.  There’s no end to the possibilities!

Here’s the original dashboard from Kamal to give you inspiration:

[IMAGE] Interactive Map with Mouse Hover Effect

#6 – Summary

» Back to contents

Did you enjoy reading about how to create a mouse hover effect in Excel?

Kamal built his map mouseover workbook to solve a business problem. He figured out how to use WMF maps in Excel, how to reference map sections, and how to update the map and display reference data.

More important than knowing how to solve each problem is this lesson. He was confident he could find answers and he kept researching options until he created a tool that exceeded his client’s expectations.

If you follow Kamal’s basic approach and have confidence in your own ability as a VBA coder, I’m confident that you, too, can create Excel tools that “do the impossible”!

Over to you…

We want to know how you’d like to use this mouseover technique. How would it impress your boss? Can you see a new dashboard you’d like to create? Let us know in the comments section below.

Here’s an incentive for commenting… If your idea is exciting enough we’ll work with you to make it into a full tutorial and template that’s 100% FREE for you. But you must leave a comment below to qualify. Explain how this would benefit you in as much detail as possible.

⚡ Need Help? If you don’t want to leave a public comment here and can’t code VBA but have a development budget – don’t worry. Get in contact with our Excel consulting team and we will turn your ideas into reality. We create custom solutions for clients around the world. You can reach our experts at this page.


#7 – About the Author

» Back to contents

[IMAGE] Chris Cox Photo

Chris Cox – Two decades ago, Chris taught himself VBA to help him eliminate annoying manual Excel tasks. Twenty years later, Chris has turned that experience into a second career as a highly-rated independent consultant specializing in Excel and Excel automation. He loves exceeding client expectations by making their processes as efficient as possible!

Chris is also passionate about writing, editing, and proofreading, and he’s successfully done all three both in full-time employment and as a consultant.

Chris currently works as a full-time as an IT analyst for a global asset management firm. He has also worked in government, healthcare, and consulting. He writes fiction in his spare time.

If you’re interested in working with Chris, please reach out to him directly at [email protected] or find him on Upwork here.


Victor Chan
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recommended Posts
Showing 7 comments
  • Victor Chan
    Reply

    Hi it’s Victor here.

    Did you enjoy reading about how to create a mouse hover effect in Excel?

    I’d like to know how you want to use the mouseover technique.

    Can you think of ways it impress your boss? Can you see a new dashboard you’d like to create?

    If your idea is exciting enough we’ll work with you to make it into a full tutorial and template that’s 100% FREE for you. Explain how this would benefit you in as much detail as possible.

    You must leave a comment here to qualify.

  • Jake
    Reply

    Wow this is super impressive, I’ll definitely keep this in my playbook to make something impressive when the right task arises!

    • Victor Chan
      Reply

      Hi Jake – nice to know you like this. When you come up with an idea come back and share it here.

  • jones
    Reply

    is it possible to have the full package in excel with the chart..? very interesting!

    thank you

  • ian
    Reply

    Would it be possible to do a plan of a building and when you roll over it shows information about each department with a picture?

    • Victor Chan
      Reply

      Hi Ian – that sounds like an interesting idea! Yes I think we could show a building plan with different areas, and when you roll over it displays relevant information about each department with a photo next to that info. I’ll send you an email to talk more about it.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.