Excel VBA Case Study #2 – Map Mouseover

 

Excel VBA Case Study #2 – Map Mouseover

 7

 9

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?

» 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:

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

#evba #etipfree #eama #kingexcel 

📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1