0

Hello World! (Sorry, I could not resist J) I was recently asked by our host, Jordan, if I would be interested in being a guest author here at Option Explicit VBA. I quickly and humbly accepted. I will strive to do my best to add something of value. Let’s dive right in.

I was inspired the other day by Chandoo’s post on his blog in regards to tax burden as well as Jared’s subsequent submission regarding service levels. Both charts use a consistent color across what appears to be different series in panel charts that are arranged closely together.

In fact, they are not, the area charts are one series with blank rows or columns inserted in the data range to create the separated effect. Here is a sample initial column chart I created using the same concept

So far, so good - but I would like each "Series" to have a different color. I selected some data points and changed the fill color

Looking good, but I'll need to manually select an additional 22 data points and change the fill color for each point. It gets worse if I want to add additional, "Series" to the chart or decide to go back and change a color - more manual work!

So I thought to myself, "Self, there must be an easier way!" The good news is that there is an easier way through VBA! Let's cook up some code (Option Explicit VBA - Remember?)

I only have one ChartObject with one SeriesCollection, so that part is straight forward. But there are many points in the SeriesCollection to be considered. Additionally, I dont want to plot anyting or add color to anything for points 13 and 25 where I have blank rows in my data.

So, I want to do something with points 1-12, 14-25, 27-38. Sounds like a good candidate for a Select Case..Case..End Select structure.

Option Explicit
1: Sub ColorDataPoints()
2: Dim wb As Workbook
3: Dim ws As Worksheet
4: Dim i As Integer
5: Dim lBlue As Long
6: Dim lRed As Long
7: Dim lGreen As Long
8: Set wb = ThisWorkbook
9: Set ws = wb.Worksheets("Sheet2")
10: lBlue = RGB(79, 129, 189)
11: lRed = RGB(192, 0, 0)
12: lGreen = RGB(155, 187, 89)
13: With ws
14: For i = 1 To .ChartObjects(1).Chart.SeriesCollection(1).Points.Count
15: Select Case i
16: Case 1 To 12
17: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lBlue
18: Case 14 To 25
19: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lRed
20: Case 27 To 38
21: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lGreen
22: End Select
23: Next i
24: End With
25: 'Tidy up
26: Set ws = Nothing
27: Set wb = Nothing
28: End Sub

Now I have a chart with one x-axis and what appears to be 3 different series, when in fact, it is one. Perhaps more importantly, I have a process that requires very little updating as my needs change to display more "Series" or to change colors.

More on the Points Collection.

Download the workbook .

How do you work with points in your charts and VBA? Let us know in the comments section.

Author: Winston Snyder Labels: Data Visualizations, VBA

0

Form controls are great for reporting information about groups of items, like a list of programs or accounts. They are often used on Excel dashboards and reports that demand interactive capabilities. One such type of capability provides the user with a list of items to choose from. When the user makes a selection, a macro is executed that populates a table holding referenced values. Those values are linked to a series of form controls on the frontend. This interactivity is displayed below:

Step 1

Step 2

Step 3

There is a final step, which I haven't included. The user would make changes to the project under the Options table. They would press a "Save" button and their changes would be copied from the Linked Values table back onto the backend data in the column corresponding to the selected project using VBA.

The No-VBA way

There's nothing wrong with this method in and of itself, but I want to propose a method that requires no VBA. The advantage of this new method is that it links directly to the data itself and bypasses the need for the Linked Values table. We can do this by allowing the form controls to take advantage of dynamic references.

Typically, form controls can only do direct, absolute references. You cannot, for example, use VLOOKUP or INDEX within the source field of a form control. However, you can use a named ranges.

Let's do it!

First, we give that 'index' field above a named. How about selection? Next, we create four named ranges to correspond to the form control checkboxes. Stage_1 to Stage_4 are those new named ranges.

As you can see from the picture, I use the fourth row to connect to checkbox Stage 4 and the selection value to inform Excel to pull from the fourth column in the backend data (which is Project 4, if you recall).

Finally, I can simply link these named ranges to their associated checkboxes:

Using this method, changes to the checkbox automatically change the backend data. There is no intermediate table required -- like the Linked Values table above -- to interface between the frontend and the backend.


That's all for now - have a happy and health holiday season!

Update 25 December:
Make sure to see the download file - Direct Links.xlsm.

Author: Unknown Labels: Interactive Dashboards, No VBA, Spreadsheet Tricks

1

Ok, so I know I promised no more updates, but this one is too cool. Basically, I combined Andy Pope's 3d rotation tutorial with the rollover method. The result is a free-floating cube which you can rotate by passing your mouse over it. Here's a choppy-ish video of what's going on. I promise however that it run smoothly on your computer. The choppiness is a result of the video's frame rate.

The mechanism is similar the the election map and the details-on-demand graphs: the chart is placed on top of the hot spots to get the mouse's coordinates. In this case, the rollover pulls in the mouse's location and initiates a timer. The timer adjusts the rotation angles based on the mouse's location. Additionally, some fancy calculations in the timer process procedure give the rotation a "slowing down" effect. I urge you to open on the Visual Basic Editor and do some dissection, if you desire. Remember though, the Timer API is a bit finicky. Make sure to save often in case your Excel crashes while in the middle of your investigations.

The download file: Rotate Cube Rollover.xlsm

Have fun.

Author: Unknown Labels: Cube, Rollovers

1

These last few months have been great to this humble blog - and to me, your humble blogger. We've received a lot of traffic, especially for posts concerning the rollover method (40k pageviews overall as of today!). Also, I got married in October! 

In pains me to tell you this, but I'm taking a break until next year (you probably already noticed my absence). In August, I signed on with Apress publishing to write a book about developing Excel applications and dashboards. I'll be honest with you - I'm woefully behind schedule, and I really need to catch up. Writing this book has been a dream of mine. So I really need to focus on it. 

In my last post, I said a tutorial on how I created the Election map would be forthcoming. At this rate, I'm not sure when that will be. Thankfully, Robert Mundigl of ClearlyAndSimply.com has written about the very same technique the Election map uses in his phenomenal article, Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard. I can't really thank Robert enough for this. His article is a pleasure to read (as are his other articles) with explanations that are thorough and thoughtful; certainly, they surpass anything I've ever written on the subject. So, if you haven't read Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard, please make your way there as soon as possible. 

Another person I need to mention is Bert van Zandbergen who has been my silent partner-in-crime concerning investigations into the rollover method. He has toyed and tampered with the rollover method producing spreadsheets that are different but always interesting. I mean, just take a look at these:

• Dynamic Circular Arrow
• Dynamic Half-Filled Circles

You can contact Bert at: klvzndbrgn (at) gmail.

Finally, the people I really need to thank are you, my readers. This blog wouldn't be what it is without you. 

I won't be gone forever. My facebook and twitter will be alive again soon. Make sure to check them out.

Oh yeah, and a happy holidays to everyone :)

Author: Unknown

11

Alright, here's another map-based rollover. The instructions are pretty simple: click on a state to toggle through its party selection. Click on one of the three selections above to change scenarios.

In the next month or so, I'll be putting together a tutorial on this. But in the meantime, have some fun. Download. Ask questions if you need.

For more on these types of maps, checkout:
Choropleth Maps with Excel

Download here:
Election Map.xlsm

How about another screenshot?

Author: Unknown Labels: Excel Rollovers, Rollovers