This is Robert's Typepad Profile.
Join Typepad and start following Robert's activity
Join Now!
Already a member? Sign In
Robert
Munich, Germany
Interests: new media, optimization, strategy, forecasting, business intelligence, project management, financial planning, business plans, telecommunications, revenue management, business performance management, simulation, pricing, intelligent data analysis, business cases, revenue assurance, cable operator, operations research
Recent Activity
Utku, this is possible. There are different options: Option 1: Format the fill color during the creation of the shapes The following line of code (line 77) currently assigns a dark grey fill color of the lines to all freeform shapes: .Line.ForeColor.RGB = RGB(100, 100, 100) If you want to have individual line colors for each shape, you need to define the color coding for the shapes (countries) somewhere on a worksheet (in tab [Data] for instance) and then change the code to use the defined line color per shape. Option 2: Format the fill color of lines after the creation of the shapes For this option, you would add a loop at the end of the code (after all shapes have been created), loop through all shapes and format the lines as specified somewhere on a worksheet.
1 reply
Greg, actually, there wouldn't be much to change in the implementation if you would want to compare e.g. a selected region of a state to the entire state. The main question would be the user interface: how would you enable the user to easily select the region he/she wants to compare to the entire state? There are options to enable the user to select regions using the mouse. Have a look here: Selecting and Highlighting Areas on Excel Charts However, this is designed for the selection of an area on a real Excel chart. Easily selecting areas which are nothing else than freeform shapes on an Excel worksheet are a completely different story. I am sorry, but I do not think there is an easy way to implement your idea.
1 reply
Russell, Jonathan Drummey wrote an excellent article about this topic over at his blog Drawing with numbers: Formatting time durations
1 reply
Jack, the tooltip code is only running when the chart is activated, so as long as the chart is not active while your other code is executed, I do not see why my code should slow down your macros. What you can try to do is to call the sub AppEventsOff at the beginning and the sub AppEventsOn at the end of your code. This should disable the tooltip functionality during the execution of your macro. If this doesn't help, you can send me a sanitized version of your workbook by mail and I will have a look.
1 reply
Steven, if you are using an XY scatter plot with lines, the lines belong to the data series of the chart just as the markers do. As you said, by definition the entire line between point 1 and point 2 belongs to point 2. The method .GetChartElement does not deliver any detailed information about whether you are above a marker or a line of the data series. You only get the information that it is the data series (ElementID) and the point index (Arg2). So, I am sorry to say, but I do not see another solution than the "clumsy and inefficient" workaround you already described in your comment.
1 reply
Lukas, this is possible, but it requires considerable changes in the VBA code. You would have to sort the data in the VBA arrays first, omit the shapes with the row headers and the row totals (because those would not make sense anymore) and then arrange the shapes according to the sorted values. You would also have to insert a legend to be able to see which colour represents which row category. As I said, not impossible, but some considerable changes necessary. The code is open without password protection, so give it a shot.
1 reply
Hi, actually, it is hard to answer your question without seeing your workbook and how you set it up. You can send it by email if you want to (email-link at the top of the blog) and I will have a look. No promise, though, that this could be within the next days, but I will try to provide my 2 cents as soon as I have the chance to.
1 reply
Hi, the code is doing this in the Workbook_Open and Workbook_BeforeClose subs (see ThisWorkbook object of the VBA Project).
1 reply
Hi, the camera object isn’t inserted by the code. I inserted it manually. The code only relocates the object and hides / unhides it. The code which updates the value in the cell named myPointIndex is in the sub myEmbeddedChart_MouseMove of the class module clsChartEvent. This line of code updates the cell: [myPointIndex] = lng_Argument2
1 reply
Hi, I am sorry, I do not have a more detailed description, but the entire VBA project has only 117 lines of code and the most important class (clsChartEvent) is commented. It should be possible to understand the implementation by reading the code and the comments. Having said that, if I understand you correctly, you want to implement customizable tooltips on a Pivot Chart. I never tried to do that, but I assume this will require considerable extra coding. The technique used above detects the index of the current data series point (the one beneath the mouse cursor) with the GetChartElement method of the Chart object. Since the data is not filtered, this index is equal to the row number in the source data of the current data point. The rest is a piece of cake. Simple INDEX formulas can then be used to display the data in the tooltip. If you are having a PivotChart, this would not work anymore, because you can’t directly refer to the underlying data source. The chart displays aggregated and maybe even filtered data and you need extra (new) code to find the data which belongs to the data point hovered over in the chart.
1 reply
Robert, I assume you set column 5 as the filter on [Control], too, (cell C7 on [Control])? Simply select "All" in the combo box and you should see all the items.
1 reply
Anna, you can simplify the formula if you do not need the filter, but why should you? Simply select All in the combo box once and for good and then delete the combo box. The cross tab will then always show all the items without applying any filter.
1 reply
Nina, Have a look at this article: Highlight Actions in Microsoft Excel I assume this is the technique you are looking for.
1 reply
Pradeep, of course this works with later versions of Excel, too (which you can easily test by simply downloading the workbook). With regards to defining different color scales: have a look at the optimized version provided in this article: Faster Choropleth Maps with Microsoft Excel
Toggle Commented Aug 1, 2018 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Hans, "On Error Resume Next" was there on purpose. If .SpecialCells doesn’t find anything (in your example formula cells returning a text), the code will throw the error. On Error Resume Next simply skips the line and the code will finish without the error. Simply put back in "On Error Resume Next" and you should be ok.
1 reply
Linda, this is not Tableau, it is an Excel workbook with VBA code (Visual Basic for Applications). The code colours the shapes of the map. To change the code, press ALT-F11 to open the Visual Basic editor, select the module modChoroplethMap in the Explorer pane on the left, search for the line of code I mentioned in my previous comment and make the changes I described. Finally run the code by e.g. selecting another state on the dashboard. This should do the job.
1 reply
Linda, in the module modChoroplethMap, simply replace .Line.ForeColor.RGB = 9868950 ' grey line color by .Line.ForeColor.RGB = C_COLOR and the borders of the states will be black instead of grey after you selected another state on the dashboard. I am sorry, but I do not understand the other part of your question. There is no such thing as a dual axis (this is not a chart, just a collection of freeform shapes) and there are no tooltips.
1 reply
Noel, regarding tooltips: please refer to the follow-up article to this post. If you want to use integer or float numbers instead of percentages, you have to adjust the bins (thresholds) defined in cell range B19:B28 on the [Control] worksheet. You would also have to change the formulas in the corresponding column D to display the legend correctly.
1 reply
Ismail, I am not sure what you mean by "only offline", but "non-commercial" does not mean that you are not allowed to use the code and template in a corporate environment. You can use the template and code in the models you are creating for work, but you are not allowed to sell the template as it is.
1 reply
Ismail, I am publishing my content under a Creative Commons License. Please refer to the link in the section "License" on the left sidebar of the blog.
1 reply
Ismail, I do not have a template of a USA MSA Choropleth Map in Excel, but you could try to find the ESRI Shape Files online and create your own Excel map. Have a look at this article: Create Excel Choropleth Maps from Shape Files
1 reply
Tim, I just double checked. The link is still working and the file is not corrupt. It is a huge workbook (45 MB, more than 400,000 data rows), so it takes a while to open it, but it is not corrupt.
1 reply
Aaron, you would call the status bar before or after each call of one of your subs. You would need to enhance the sub a little bit and not only pass the total number of iterations, but also the current iteration as a parameter and delete the For Next loop inside the code of the progress bar sub. As you mentioned, some subs will probably take longer than others, so displaying a percentage value wouldn't make much sense. Instead, you could display something like "step 1", "step 2", etc. Another idea would be to have two progress indicators: the first showing the step (i.e. which subroutine is running at the moment) and the second one displaying the percentage of completion inside this subroutine (starting by 0 again when calling the next routine).
1 reply
Alyssa, please have a look at my reply to John’s question on January 13, 2016 at 5:41 PM.
1 reply
Way, here are the main steps: 1. Adjust the data to have only data from 2011 to 2017 2. Make sure the combinations in column ID (Country/Year) on the data sheet are adjusted, too 3. Adjust the values of myMin (=2011) and myMax (=2017) on the control work-sheet 4. Set the value of myCurrentValue to 2011 (as a starting point) 5. Rename the rectangles on the dashboard: Rectangle_2013 becomes Rectangle_2017, Rectangle_2012 becomes Rectangle_2016 and so forth 6. Delete all not necessary rectangles on the dashboard (Rectangle_2006 and earlier) 7. Adjust the axis labels of the rectangles 8. Resize / reposition the rectangles as you like This should do the job.
1 reply