Excel VBA Case Study #2 – Map Mouseover

Last updated on February 15, 2019 By Victor Chan

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

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

#1 – What makes this workbook special?

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?

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?

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

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

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:

#6 – Summary

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

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 ctcox20@gmail.com or find him on Upwork here.


Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.