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
Juan, did you also resize the ActiveX label control?
1 reply
Angela, I used the technique with Excel 2010 / Windows 7, Excel 2013 / Windows 7, Excel 2016 / Windows 10 and nowadays with Excel 365 / Windows 10 and never had the problem you described. You can send me your workbook by email and I will check if I can reproduce the error on my machine, if you want.
1 reply
Savy, you have to adjust the range of the name [myProjectInfoRange] in the Name Manager.
Toggle Commented Mar 25, 2019 on Bring your tasks in a row at Clearly and Simply
1 reply
Savy, I can’t reproduce the problem. I just checked and exported a Project task with 10,000 words in the notes (more than 60,000 characters) to Outlook and it was correctly and completely exported. Not truncated, neither at the beginning nor at the end. I have no clue why this does not work for you.
1 reply
Savy, this is possible, too, but it requires quite some additional coding. The code would first run through all selected project tasks and create a unique list of all resources, the tasks have been assigned to. Then it would run through this list of resources and all selected tasks and compile a list of tasks for each resource. Finally, it would run through all resources again and send one Outlook task containing the information of all Project tasks to this resource. Not impossible, but a lot of extra coding necessary. The code is unprotected, so please feel free to adjust it as you like.
1 reply
Savy, If you want to create a task and an appointment, select the tasks and simply click on Selection to Outlook Tasks first and then on Selection to Outlook Appointments. If you want to save the second click, you can also combine the code inside the two VBA procedures into one.
1 reply
Scott, in the example workbook posted above, an Active X Label and a XY Scatter chart are sitting on top of the map (which is a group of shapes). If you want to get rid of the map, simply open the Selection Pane (ALT-F10), click on the group "Land and Sea", press Delete and the map is gone.
1 reply
K.O., many thanks for your comment. You are absolutely right: if you only change a small fraction of the values, writing them directly to the cells tends to be faster than populating the entire array to the range. If you take this to the extreme, changing only one cell is certainly faster than writing an array with a couple of thousand elements, even if you would use the Select statement (which you never would do, would you?). But where is the threshold / break-even? I haven’t done a detailed analysis, but here is an educated guess: method 6 (writing the array in one go) is usually around 90% faster than method 3 (cell by cell with a range variable). My wild guess would be that the threshold is probably somewhere around 10%. If you are changing less than 10% of the values, you will be faster writing them directly to the cells. If your code changes more than 10% of the cells, writing the array in one go will probably be the better choice. As I said, just a wild guess, no detailed analysis done. And it does not consider the additional time the code needs to decide whether to write directly or to populate the entire array. Finally: I cannot reproduce the numbers you mentioned in your comment. Writing an array with 2,000 rows and 30 columns to a range takes 0.15 seconds on my machine. Even 20,000 rows and 30 columns do not take more than 1.6 seconds. I can’t reproduce the 6 seconds you mentioned but this may have to do with your setup or implementation. Hope this helps a little bit.
1 reply
Colijn, simply add the following 2 lines to the code (before the .Save statement): .ReminderSet = True .ReminderTime = .DueDate & " " & CDate(#8:00:00 AM#) These two lines set the reminder to 8am on the due date of the task.
1 reply
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