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
Henk, sure: 1. in the worksheet object model: in the IF clause beneath the SELECT statement, delete "Or Target.Value = C_NA" from the IF condition and add it to the ELSEIF condition 2. in the sub ChangeTopicStatus of the module, add "Or varData(lngRowCount, lngActiveCol) = C_NA" to the condition of the third (the innermost) IF clause This should do the job.
1 reply
Mike, this is possible (of course) with a few extra lines of code. Have a look at this workbook: Download check_list_log_file.xlsm (35.9K) It is based on the original check list and writes all changes to a log sheet. To keep it simple, it is coming with one disadvantage, though: if the user double clicks on a topic, only the change of the topic status is logged, not the (automatic) changes of all its items. You could enhance the code to improve this (if you want), but I think this workbook should give you a jump start. Hope this helps.
1 reply
Enrico, you are absolutely right, this was actually a bug in the workbook I originally posted in my reply to Brian. It was simple to fix, though. I only forgot to adjust the second parameter passed to the UDF StatusCompletionRate. I fixed this now. Please download the workbook again and it should work. Thanks for pointing this out.
1 reply
Enrico, have a look at my reply to Brian's comment on October 10th, 2016 above. In my answer to Brian, I included a download link to a workbook which provides what you are asking for.
1 reply
Sophie, it should work if the plot area of the chart and the image have exactly the same size and the same top and left corner. Having said that, it is indeed very cumbersome to align the two objects. Before I published this post, I was trying to do this automatically every time the workbook was opened or the sheet was activated. I wrote this code snippet: Sub Align_PlotArea_and_Image() With ActiveSheet.ChartObjects("Chart1") ActiveSheet.Shapes("Image1").Width = _ .Chart.PlotArea.InsideWidth ActiveSheet.Shapes("Image1").Height = _ .Chart.PlotArea.InsideHeight ActiveSheet.Shapes("Image1").Top = _ .Top + .Chart.PlotArea.InsideTop ActiveSheet.Shapes("Image1").Left = _ .Left + .Chart.PlotArea.InsideLeft End With End Sub It works ok for the size of the image, but for some reason (which I do not understand), it does not work for the top and left position. However, after you ran the sub, you are already pretty close and can do the rest manually by repositioning the image. Hope this helps.
1 reply
Jon, first group the shapes (e.g. S_USA and S_CAN) and give that group a new, unique name, e.g. S_NA. Then insert a data row somewhere inside the data table and assign the name D_NA to the according cell of this new row in column K. Finally go to the control sheet, insert a row somewhere in the range which assigns the names to the shapes and assign S_NA to D_NA. If you then run the algorithm (by selecitng another measure), USA and Canada will be colored according to the data you inserted in the data row added in step 2. Hope this helps.
Toggle Commented Nov 18, 2016 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Sophie, I recently noticed a problem with the ActiveX label control, too. Even if the background of the label is set to transparent, it seems to be opaque after you turned off the design mode. The technique used to work well, but it doesn’t anymore. I have no clue why, probably a new bug of the label control which came with one of the latest Microsoft Office updates. My suggestion: you can use an image instead of a label control. The ActiveX image still seems to work fine, but there is no guarantee this will last forever. You would have to replace the label control by an image control and change the VBA accordingly to address the image instead of the label. Hope this helps a little bit.
1 reply
Mike, there actually may be some smaller distortions during the procedure of ungrouping an EMF file to freeform shapes, but I can't reproduce what you are describing. I just opened the World Map in Excel 2016 and there is no noticeable gap between USA and Mexico. If your version of Excel distorts the map, you may want to create your own version from scratch following the instructions e.g. in this article: Build your own Choropleth Maps with Excel
Toggle Commented Nov 9, 2016 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Sue, have a look at this thread on the Tableau Community Forum: https://community.tableau.com/message/180164 I think Richard Leeke is answering your question there and he is kind enough to even provide an example workbook with a step-by-step explanation.
1 reply
Ajay, let me start with one important point for clarification: if you want to use the code in all of your workbooks, you either have to copy the code to all the workbooks, define the name "myData" (manually or by VBA code, see below) and save them as macro enabled workbooks or you have to transfer the code to an add-in (probably using a new class module providing the Worksheet_BeforeDoubleClick event). As per your question how to programmatically add and delete a name: the following line adds the name "myData" referring to the used range of the first worksheet: ThisWorkbook.Names.Add Name:="myData", RefersTo:=Worksheets(1).UsedRange The following line deletes the name "myData": ThisWorkbook.Names("myData").Delete I hope this helps.
1 reply
Terri, sure, send me your workbook by email (see Email Me-link at the top of the blog) and I will have a look as soon as possible.
1 reply
Sue, if you want to concatenate all texts of one dimension separated by a line break, you have to create the following calculated field: PREVIOUS_VALUE("") + CHAR(10) + ATTR([Text]) [Text] is the dimension containing the individual texts. The last record of this calculated field will contain all texts in the database separated by a line break. I hope this helps.
Toggle Commented Oct 28, 2016 on String Calculations in Tableau at Clearly and Simply
1 reply
Diletta, if you can't find Italy's polygon data for download on the web, you can download ESRI Shape Files e.g. from here: http://www.gadm.org/country and use Richard Leeke's ShapetoTab tool, described at the end of this article: Create Your Own Filled Maps in Tableau ShaptToTab transfers the shape files to the polygon data and provides CSV files you can then directly copy into Excel.
Toggle Commented Oct 27, 2016 on Cartograms in Microsoft Excel at Clearly and Simply
1 reply
Onditi, unfortunately: no, I have never done anything in the field of sports results prediction. And even if I would: don't get me wrong, but if I would have a model at hand reliably predicting the results of future football matches, I don't think I would share it for free.
1 reply
Matt, it has indeed something to do with the named range myChecklist, but also with the fact that an important part of the VBA code (the Worksheet_BeforeDoubleClick sub) is not in a module but in the worksheet object. I assume, you inserted a new sheet and copied the content of the first sheet to this new sheet. This is not sufficient. You also have to define the range name on the inserted sheet (scoped to this worksheet, not to the workbook) and copy the entire code of the worksheet object of the first sheet to the worksheet object of the inserted sheets. The easiest way of getting this to work is not to insert new sheets and copy the content of sheet 1, but rather to make a copy of the entire worksheet 1. This way, the sheet scoped range name will automatically be created and the copied sheet will also contain the worksheet object code. Just click on the tab of the first, keep the CTRL key pressed and drag the sheet to the right and you should be alright.
1 reply
Anne-Sophie, if you get a "runtime 424 object required" error already at the line calling the main sub CreateMarimekko, I would assume one of the named ranges passed to the procedure is missing in your worksheet or may be misspelled. Please check if the names "myChartArea", "myData" and "myColorScheme" do exist in your workbook and are spelled correctly. If this does not solve the issue, please send me your workbook by email and I will have a look.
1 reply
nareshnani211, 1. insert new columns between colum H and I. 2. Copy the entire column H to the inserted columns in step 1 3. Increase the second argument of the UDF myCompletionRate in the cells right to cell H3 from 7 to 8, 9, 10 etc. This should do the job.
1 reply
Brian, have a look at this workbook: Download Check List more Cols stamped (36.7K) This is a combination of versions 5 and 10.
1 reply
Norbert, I think I answered this question in my reply to Diego (see above), didn’t I? Or am I misunderstanding your question?
1 reply
Mohammad, I suppose, my readers are – just like myself – primarily using Microsoft Excel and not Google docs. I would assume, your chances to find someone here transferring my code to Google Apps script are next to nothing. I recommend to publish your request on a Google Apps script forum.
1 reply
Mohammad, maybe my answer wasn’t precise enough. I am sorry. To be crystal clear: there is absolutely no way to make my code working on a Google docs sheet. My code is written in Visual Basic for Applications, the programming language dedicated to write code for Microsoft Office applications like Excel, Access, Word, etc. Google Apps script is a completely different programming language. If you want to have the functionality on a Google docs sheet, you can't use anything of my VBA code in the Excel worksheet provided above, not one single line. You have to write your own Google Apps script code from scratch.
1 reply
Anil, actually the interactive drop lines in the workbook posted for download above work only on charts with only one data series. Having said that, you can enhance the tem-plate by duplicating and adjusting all named formulas and with a few small changes in the VBA code. Have a look at this example with an XY scatter chart and 2 data series: Download Interactive Drop Lines XY Scatter with 2 Series (36K)
1 reply
Mohammad, the features are implemented in VBA (Visual Basic for Applications), Microsoft's programming language for Office applications. As is, they work in Excel only. It may be possible to rebuild the same functionality in Google Docs with Google's Apps Script, but I do not know. I am not familiar with Apps Script.
1 reply
Mohammad, have a look at this file: Download fil-ter_table_by_double_clicking_selected_columns (39K) You can specify in the row above the column headers, which columns shall be viable for filtering by double clicking. If you set the value above a column header to TRUE, you can filter by double clicking, if the cell value is FALSE, the double click feature is not available in this column.
1 reply
Giuseppe, I am sorry, I do not understand your question. What do you mean by "remove control checkbox"? What exactly do you want to remove?
1 reply