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
Steven, thanks. I am glad you find this useful. I assume the run time error message is "The item with the specified name wasn’t found", correct? If so there is probably at least one mismatch between the definition of the names in "myShapeNames" and the names of the shapes on the map. Maybe a trailing space, a typo, a missing character, etc. Let the sub run again, click on Debug and in the VBE hover over the part "…varShapeNames(lngCount…" of the yellow line with the mouse and you will see the name of the problematic shape in a tooltip. Then go to the worksheet(s) and make sure the shape name matches with the definition of the name on the data worksheet. You may have to repeat this several times, if there is more than one mismatch. If you can't find the issue, please send me your workbook and I will have a look.
1 reply
ptarlow, go to Excel's Name Manager (on the FORMULA tab or click Ctrl-F3). Select myCheckList, go to the "Refers To" section at the bottom of the Name Manager and select the range of your check list. Click close and yes.
1 reply
Sastry, send me your YML file and the link where you downloaded the shape files from by email and I will have a look.
1 reply
Jeff, I am sorry, but I do not even know what ITV zones are. I am German. I am sorry.
1 reply
Leon, thanks for your comment and your kind words. You are on the right track with your line of code. Simply replace the constant ppAlignCenter by its value (i.e. 2) and you should be good to go.
1 reply
Image
Create dynamic Word Clouds / Tag Clouds in Microsoft Excel Continue reading
Posted Feb 24, 2015 at Clearly and Simply
Jodi, this is not a bug, it is the approach I used in the code. In order to keep the implementation as simple and fast as possible, the code reads the entire checklist into a VBA array, makes the changes in the array and writes the entire checklist back to the worksheet in one go. If you have a formula inside the named range myCheckList, the code will overwrite the formula and replace it by the value. The easiest way to solve this would be to keep the formulas outside of myCheckList, but I think this isn't possible in your case, since you are trying to aggregate items by formulas, i.e. you need the formulas inside the checklist. I.e. you have to change the part of the code which writes the checklist back to the sheet. You would then write back only the column(s) which changed (time stamp and check boxes), not the entire list.
1 reply
Jodi, to transfer the checklist from one of my templates into another workbook, you have to conduct the following steps: 1. Copy the entire worksheet check_list to your workbook. Don't copy the cell range. Rather click on the sheet and drag the entire sheet to your workbook. This way, the worksheet object code and the named range myCheckList will be transferred to your workbook in one go. 2. Open my template again (it was closed after step 1, since you moved the worksheet to your workbook) and go to the VBE (ALT-F11). In the Project Explorer (the window at top left of the VBE) click on the module modChecklist of my template and drag and drop it to the VBA project of your workbook. 3. Save your workbook as a macro-enabled workbook (.xlsm). 4. Close your workbook and open it again. It could be that Excel turned the calculation option to "manual" again. In this case you already know what to do…
1 reply
Jodi, 1. To check the boxes by simple clicking (i.e. selecting a checkbox) instead of double clicking, you have to use the event sub Worksheet_SelectionChange instead of Worksheet_BeforeDoubleClick in the worksheet object. The code inside the sub is the same, you only have to delete the line Cancel = True at the end of the sub. 2. I can't reproduce this in the workbook I posted for download (workbook #8). I am not sure what is going on in your workbook, but I would assume you set the Calculation Options on the FORMULA tab to manual. If so, set the calculation to "Automatic" and it should work.
1 reply
Scott, it would be possible in general, but there is an issue with the approach I have been using. I am defining the color palette to be used in the Marimekko Chart in a cell range called myColorScheme on the worksheet control. The VBA code uses the fill colors of the cells for coloring the freeform shapes of the "chart". This works well for colors, but it does not work for fill patterns, since the fill patterns of cells and shapes are different in Excel. I.e. you cannot simply assign the fill pattern of a cell to the fill pattern of a shape. Thus, you would have to change the entire approach, i.e. use shapes on the control worksheet to define the colors and patterns of the chart instead of cells. This is possible and no rocket science, but it requires considerable changes in the code. Having said that, I would not recommend your idea anyway. The chart shows the values inside the boxes. Using patterns would make those labels much harder to read.
1 reply
Omer, this is an article published in 2012 using Tableau 7. Since version 8, displaying a PDF in a webbrowser on a dashboard doesn't work anymore. The URL action opens your default browser showing the PDF instead. Displaying folders (see example #7 in the article) doesn't work anymore either. Please see also my reply to aalok's comment above.
1 reply
Brian, if it is just a smaller code snippet, you can post it here in a comment. If it is more code or the entre workbook, you can upload it to e.g. your Dropbox or OneDrive and post a link to the file here or - as already suggested - you send it to me by email (email link at the top of the blog).
1 reply
Brian, use the template #12 as the master and open both workbooks. 1. Insert a column between D and E in template #12 2. Go to the VBE 3. Search for "Now()" in the module modCheckList of workbook #4 and copy the entire IF THEN ELSE clause which contains "Now()" 4. Go to the module modChecklist of #12 and insert the IF clause at exactly the same position as it is in #4 5. Change the -1 parameters (all of them) in this IF clause to -2 (since the time stamp is now 2 columns left to the check box column) 6. Go to the sheet object in workbook #4 7. Search for "Now()" again and copy the IF clause around it 8. Go to workbook #12 and insert the IF clause at the same place. Again change -1 to -2 9. Four lines above this new IF clause, insert the following line: Target.Offset(0, -2).ClearContents This should do the trick.
1 reply
Brian, send me your workbook by email and I will have a look.
1 reply
Norm, this is an annoying issue and many readers had problems with other download links in other articles, too. So, thanks for pointing this out again. One minor remark: as far as I know only Microsoft's Internet Explorer changes the extension to .zip when downloading. If you right click and select "save target as" in Firefox or Google Chrome, you download an .xlsm file and can open it by double clicking without the need of changing the file extension.
1 reply
Dexter, you have to adjust the "refers to" of the named range "myCheckList". Go to the Name Manager and make sure "myCheckList" covers your entire list. If it is still not working, select the cell with the completion rate, press F2 and return and it should work.
1 reply
Alberto, I downloaded the files and I can reproduce the issue with Indiemapper. First of all: I am not familiar at all with Italy's administrative or zip code regions, but I don't think the shape file provided in your download link contains the zip codes. It seems to be a map of Italy by counties (commune). If you are looking for an Italian map by counties, you can download the shape files from this link: http://www.gadm.org/country The shape files provided there work well with Indiemapper.
1 reply
Saket, I published this article back in 2012 and in the meantime, Google requires to wrap up the URL in an iframe and to pass a mandatory API-key. Have a look at the Google API documentation here: Google Maps Embed API
1 reply
Einar, yes this is possible. All you have to do is to copy the subs ExpandAll and Collapse All from workbook 13 into workbook 5, insert two command buttons and assign the two macros. As per your second request: yes this is possible and it shouldn't be too complicated. You would have to add a few lines of code creating a stamp for the topic in the subs ChangeTopicStatus and AutomaticSetTopicStatus. Unfortunately I do not have a ready to use workbook for you. As I mentioned in the article, there are countless possible combinations of the techniques and I do not have the time to create them all. Have a look at the VBA code, I am sure you will figure out how you can do it on your own.
1 reply
TK, I double checked and the links are still working for me, in IE and Firefox. Please try again.
1 reply
Saeed, I am not sure I understand your request correctly, but you may want to have a look at the compilation of check lists I recently published in this follow-up post: Microsoft Excel Check List Compilation Have a look at check list no 12. I think this could be what you are looking for.
1 reply
Image
A compilation of variations and enhancements of the interactive Microsoft Excel Check List Template Continue reading
Posted Jan 20, 2015 at Clearly and Simply
24
Gulgun, first of all I would recommend to use the faster version of the algorithm provided here: Faster Choropleth Maps with Microsoft Excel Same map, but it updates the colors 10 times faster than the algorithm provided in the article above. You can adjust the number of color bins and the thresholds on the worksheet [Control]. Delete the rows you do not need and adjust the thresholds as you like.
1 reply
Jeff, hard to say without seeing your workbook. Did you update the named ranges, too? Maybe it is a problem with the names. If you want, you can send me your workbook by email and I will have a look.
1 reply
Darius, unfortunately I do not have a 64Bit Excel version available, so I didn't know that. Many thanks for pointing this out and even providing a solution. This is greatly appreciated.
1 reply