Using Dependent Form Control Combo Boxes and Validation Cells
with Named Ranges for Cross Platform Excel Development:
Other Cross Platform Issues:
Return To Top
Using Dependent Form Control Combo Boxes and Validation Cells with Named Ranges for Cross Platform Excel Development
April 5, 2014
I am developing cross platform Excel code to be used by fellow faculty, chairs, and deans. I found that Validation Cells did not offer a good user experience on the PC so found a solution as described below. Following are screen shots of the two worksheets I use in the example workbook.
On the left is the main page. Cells A1:C3 are the results of making choices with the Form Control Combo Boxes, also known as DropDown boxes. Hidden under the Combo Boxes are the associated Data Validation cells. The selections in the bottom Combo Box depends on the selection made in the middle Combo Box. The sentence in row 13 is formed by using the Data Validation cell informtion.
On the Lists page is where all the named ranges are stored.
Debra Dalgleish, author of the amazing Excel tutorial site, http://www.contextures.com, gave me the idea of using Combo Boxes along with Data Validation cells in my Excel programs in her Excel Tips, http://www.contextures.com/xlDataVal11.html. However, due to my cross-platform constraint, I could not use her method as presented. Instead of using ActiveX controls, which are not implemented on the Mac, I used the Form Control Combo Boxes, usable on both platforms. I develop Excel codes in Excel 2011 on the Mac but most users of my codes use Excel 2010 on a PC. On the Mac, Data Validation cells offer an easy to use interface. However, on the PC, they only show eight rows at a time and the font is quite small. DropDown boxes still have somewhat small text but no row constraint. Screen shots of both are shown in the next image. The Mac screen showing the use of Data Validation cells is on the left and the PC screen showing the use of the Combo boxes is on the right.
The Excel 2010 example was generated using Excel 2010 inside of CrossOver on my Mac.
In addition, Debra's site shows how to have dependent Data Validation cells by using named ranges and the INDIRECT function http://www.contextures.com/xlDataVal02.html. I implemented the same concept in the DropDown boxes approach.
Download Source Files
The code is available as an Excel xlsm file. For those of you who prefer to not download files containing macros, the only macro used is available as a .bas file and the workbook is available as an .xlsx file, i.e. no macros.
Return To Top___________________________________________________________________________________________________________________
Creating a mind map and a SmartArt organization chart of procedure calls in VBA
Part I June 27, 2012
In the process creating a somewhat complicated Excel VBA workbook I found I needed a code that would give me a procedure flow chart, i.e. what procedure calls or references what procedure. With alot of help from a couple of sites (URLs are in the code itself), I put together the following code (global declarations and code module). Note that the code does not handle user-defined classes as I do not use them.
To use the code, open the VBA editor in Excel. Import both bas files into an Excel VBA project then run the first subroutine, genProcedureFlowChart_Main() at about line 14 of the module. If the sheet MacroFlowChart does not exist, the code creates it. From then on as you develop your code further, you can run this code to keep straight who's calling whom. If you want to see it work, download my test file with lots of code and modules in it. BBEdit and TextWrangler (Mac text editors) will show line number if you copy and past module contents into it. Note that the information is now in a format that can be used in mind-mapping software or XML databases.
I am honored that Debra Dalgleish presented my code in her Contextures Blog.
Note - you are free to use my codes. You are not free to sell any of it. Most of it is derived from code found at ozgrid.com, exceltip.com, and cpearson.com. Please feel free to contact me with questions or errata.
Return To Top
Part II August 9, 2012
The end of the above project is to take the list of calls and turn them into a mind map. Then you can follow the procedure flow logic visuallly. I made a small example that shows you what this part looks like. It is not yet connnected to the above code as I need an iterative solution instead of stackoverflow.com's recursion method of producing the tree. In my larger testbed, the recursive solution ran out of stack space. I have, however, included classes as well as programatically created charts and forms.
The overall flow of this part of the code development looks like
The orange area contains the list of procedures and which procedures they call. The code below takes that list and creates a mind map file that can be read my almost all mind mapping programs available. I use Freeplane. Its associated schema is on the Freeplane website.
Before generating the tree, my worksheet looks like the following.
After the code is run, the tree is filled in and the mind map file created. The code in the mind map file is to the right of the tree. The map itself is created in the same folder as your workbook with the same name but has the .mm extension.
The code to generate the above result is given in two files to be imported into the VBA editor: sheetSetUp.bas and Tree2MM.bas. The code in the first module generates a new sheet in a workbook, then sets up the pre-tree worksheet. Run the first procedure in sheetSetUp.bas: createFormattedSheet. That routine will ask you to do a SaveAs on the workbook as the name of the workbook becomes the name of the mind map file. Once that setup code has been run, the VBA editor hides itself. Then you can make the tree and associated mind map file by clicking the Make the Tree button which runs the code in the other module. Thanks to stackoverflow.com for providing the code that recursively generates the tree from the list of calls.
The above method avoids your downloading code with macros in them. As far as I know, my testbed file, ToUHSite.xlsm, has no nasties in it so feel free to download it if you don't want to go through the hassle of setting up your own page.
I occasionally update my flowchart code as I try to use it in different codes I am developing. Here is the latest version (01/26/13).
makeMMTree.bas, genProcedureFlowChart.bas, Globals4FlowChart.bas
Newer versions that include org charts are available below. See the "Flowcharts from the CETA Scheduler June 8, 2014" section.
Return To Top
Part III May 28, 2014
I am still trying to document a fairly complicated code and found the mind maps created above are interesting but not terribly useful for tracking procedure calls while undergoing code development. It required too much distraction for me to load up another application and get its file. I prefer to just stay in Excel.
In my documentation code, I will create a version that, when you click a button, instead of executing the code, the code in the documentation workbook will create an organization chart of the code calling sequence for you in Excel itself. I found very little documentation on the web about how to create organization charts in Excel VBA using SmartArt. The two codes I did find were on the following web sites. The second reference I also found on many other sites. The first uses non-recursive code and the second, recursion.
Thanks to the examples from those sites, I was able to find the code needed to include organizationn charts in my previous tree code based on stackoverflow.com, replacing the code to generate the mind map with that of creating the organization chart. The results follow. In my example files I chose to create a family tree as it would be easier to recognize names than procedure calls. Note that the chart is single parent as I am really using it to create a visual graphic of code documentation.
Before generating the tree and organization chart, my cleared worksheet looks like the following.
When I press the Make the Tree button, the code fills in the empty cells.
Beneath this chart is the organization chart. Originally it was SmartArt but I found it more convenient to convert it to a picture as I did not need the interactive aspects of SmartArt, just an organization chart, here presented as a form of family tree.
The choice of which type of chart to create is given in the main program by the following VBA code.
Set oSALayout = Application.SmartArtLayouts(88). See addendum below. The numbers in Excel 2011 are different from those in Excel 2010.
I also found a format I liked that was horizontal. To generate the following on the Mac,. I used Set oSALayout = Application.SmartArtLayouts(95) .
The code and spreadsheet are available in two formats. The full xlsm file is Tree_OrgChart_For_Website.xlsm. If you prefer to build the macro-enabled workbook yourself, then you can download the xlsx file, Tree_OrgChart_For_Website.xlsx, and the macro code, TreeOrgChart.bas, separately, then import the macro into the macro-free workbook. You can find the mindmap file at Tree2OrgChartandMM.mm.
Return To Top
Part IV June 6, 2014
I have now incorporated the mind map code into the org char code. The resulting mindmap is
The new code downloads follow: TreeOrgChartandMM.bas, Tree2OrgChartandMM.xlsm, and Tree2OrgChartandMM.xlsx.
Addendum June 8, 2014
Thanks to Debra Dalgleish (contextures.com) for finding yet another PC vs. Mac difference.
1) The Heirarchy Chart numbers differ. I have generated a document showing the charts with PC numbers and Mac numbers.
2) When the code creates a picture from the SmartArt, the PasteSpecial command differs on the PC and the Mac. I tested all three PC formats on the Mac and Excel 2011 did not complain. Hence I will have the code default to PNG since then PasteSpecial works on both.
I have updated both the above org chart codes to become cross platform.
I am honored once again that Debra Dalgleish presented my latest work in her Contextures Blog.
Flowcharts from the CETA Scheduler June 8, 2014
I have updated my code from above in Part II to include org charts which I am using as flowcharts for my code
makeMMTree.bas, genProcedureFlowChart.bas, Globals4FlowChart.bas - Last updated 07/31/14.
Having just used the code extensively, I found that the mind map file sometimes looks like it is missing branches. I have a procedure tree with 95 lines of calls in columns G, H, I, J, etc.on the Tree2MM worksheet. However, I found that the tree branches are just not expanded in Freeplane. Hover over on the little white circles at the end of lines then click on the plus sign that become visible to the expand the tree. Another note: some of the routines I am looking at, such as the one just mentioned, make huge org charts so when I think the mapping is taking too long, I hit ESC, then turn off the option to create an org chart. Then the tree with associated mind map file is then built quite rapidly. When I turned org chart generation back on, the result was unreadable. The mind map, on the other hand was very readable and useful. Each graphic format has its use. Note that I had to append the file type of .txt to the mind map file to make it accessible. Download the file then remove the .txt to restore its file type to .mm.
Using the modules in your code:
Import the files into the VBA editor of your code. To run the code that creates the two new sheets full of data, run the procedure called genProcedureFlowChart_Main at the top of the genProcedureFlowChart.bas module.
You will need to activate the Extensibility VBA tool to access the VBA Extensibility Model. On the Mac, the tool dialog box is found in the VBA editor under Tools/References.
The current module allows me to use the existing Class Search data, which is a list of current course offerings at UH, present it in both graphical and textual formats, and modify the schedule locally. I am mostly through with the project. As indicated above I need validation cells for a good Mac experience and form control Combo Boxes for a good PC experience. The Scheduler code is very complicated. The validation cell version works fine but I now need to implement the Combo Box, a.k.a. DropDown, version for cross platform compatibility, in my latest addition to the functionality of the code. As the module development progressed, code became obsolete, cells moved, etc. I thought I would show you a couple of flow charts I created today so I can figure out how and where I need to integrate the DropDowns with the final validation cell usable code. When the full code is ready later this summer, I will post it here with a user guide. In the meantime, feel free to contact me about the flow chart code if you wish to implement it. Note that I do not address user created classes as I do not use them.
Two of the more complicated flow charts from my code follow. The top line is the module name and bottom line is the procedure name. The SmartArt organization chart typically needs adjustment before copying it into a picture. I therefore added a button on the Tree2MM sheet to do the conversion once I have resized the SmartArt to reduce white space and to adjust the titles so they are readable.
All my codes are a work in progress. They are also all open source so feel free to contribute ideas, code, etc. for improving on my work. If you use my work in your own code, please give me credit as I have given others credit in the code itself. If you unearth errors, please let mw know ASAP so I can address the issue(s).
Return To Top
June 19, 2014 - More Mac/PC differences
While working on my cross platform code I happened to choose a colorindex of 32. When I checked the code in Excel 2010, I got quite a surprise! I ran the following code on both Mac and PC. Below are the results.
Sub colorTheCells() ' in column I
Dim iLoop As Integer
Dim iRow As Integer
For iLoop = 0 To 56
iRow = iLoop + 2
Cells(iRow, 9).Interior.ColorIndex = iLoop
The Mac version (Excel 2011) is on the left and the PC version (Excel 2010) is on the right.
Some good color sites I have found are
Another difference I see is that I use 100% zoom on the Mac for all my development. When I run the same code on the PC, I have to set the zoom to 65% to see essentially the same size on my screen. I now have my Workbook_Open() procedure set to run the following code.
Dim wsCurrent As Worksheet
Dim wsStartup As Worksheet
Dim sep As String
sep = Application.PathSeparator
Application.ScreenUpdating = False
Set wsStartup = ActiveSheet
Select Case sep
Case "\" 'it's a PC
For Each wsCurrent In Worksheets
ActiveWindow.Zoom = 65
Case ":" 'it's a Mac
For Each wsCurrent In Worksheets
ActiveWindow.Zoom = 100
MsgBox "The OS is neither for a Mac or a PC. "
Application.ScreenUpdating = True
Return To Top
July 29, 2014 - optional printing to the OS default printer or to a PDF file.
My Scheduler needs the ability to write to a default printer to generate handouts as well as generating PDFs that can be combined using Acrobat then sent to interested parties. Of course, there are cross platform issues. The details of the issues are in the file I wrote to demonstrate using the printing options. An image of the Main sheet follows.
As usual, I provide the xlsm file as well as the code and interface separately for those who do not like to download macro-enabled files. The main workbook is
Printing PDFs on Mac and PC.xlsm. The macro-free version is Printing PDFs on Mac and PC.xlsx. The code module is PDFPrint.bas. The code to run the setup is in the ThisWorkbook code.
Private Sub Workbook_Open()
Once you have imported the bas file and updated ThisWorkbook, save it as an xlsm file. Then quit Excel itself on the Mac or just close the file on the PC. Reopen the file to start using it. There are issues on the Mac so you need to clear out Excel's memory.
Below is a screen capture of the pdf sample printout.
I found I needed to use the concept of a list validation cell on a form. I could not find any information on the web that dealt with list validation cells, only data format restrictions for combo boxes on a form. My form fills a combo box with a named range. The solution was simple. When I press the OK button to return to the spreadsheet, I look for an error in the Match function. If the combo box entry is not in the list, it won't be found by Match. If that's the case, the code sends you back to choose from the list in the combo box. You can also just cancel the form.
There is a way to restrict data entry as a property of the combo box. For the combo box if Matched Entry is True and Required, you get the following error if there is a problem when you leave the combo box.
I could find no way to change the error message so it was easier to understand. In addition, I only care when the form is exited, not when the Combo Box is exited. Thus I came up with using the Match worksheet function. The cmdOK_Click code associated with the OK button follows.
Private Sub cmdOK_Click()
Dim rowNumber As Integer
' chosenNumber = -1 is set when the form loads.
If chosenNumber = -1 Then
MsgBox "Please select a number."
' do a check to make sure the number is in the list
On Error GoTo Oops
rowNumber = WorksheetFunction.Match(chosenNumber, Range("Numbers"), 0)
ActiveSheet.Cells(7, 7) = chosenNumber
MsgBox "Your choice is not in the list. Please select a number from the list."
The xlsm file: FormValidation.xlsm
Load the spreadsheet using an xlsx file with form and code data: FormValidationInfo
This link takes you to another URL with screen shots of all the form element properties, the xlsx file, and the VBA code.
Return To Top
How to Generate a filtered Table to be used as a Pivot Table source file.
August 22, 2015
Last spring my chair asked me how many sections of a math course should we offer in the fall based on previous enrollments. That request started a landslide of my learning Tables and Pivot Tables in Excel. I found that Pivot Tables, clearely the solution, have a drawback for my type of data. The filters show all unique entries in a table column. They are not dependent on other filters in the Pivot Table. That's fine for business where you can group dates into months, years, etc. However, dates are not part of my table. I have nothing to group. I have fourteen semesters worth of data. In that table the unique entries of various fields are
Clearly I needed to somehow filter the table to be usable by one college without looking at another college's data. The result is that I have developed the code to filter the master table with 47311 rows of data to create a college table containing only data relevant to that college. Actually, the code I developed used a smaller data set for development found at http://contextures.com/xlPivot-Filter-Source-Data.html. My application
1) reads a typical advanced autofilter criteria Table limited to six rows for the testing process,
2) creates the single column Table entry in the master data Table for filtering,
3) filters that column to TRUE,
4) copies the visible rows to another worksheet, and finally
5) makes a new Table with just the copied rows.
Much more detail is given on the Notes sheet in the workbook. You can download the workbook, TablesForPivotTables.xlsm, or download the macrofree workbook, TablesForPivotTables.xlsx, and the code module, FilterTable.bas to import into VBA.
The files with all the testing removed are TablesForPivotTables_Trimmed.xlsm, TablesForPivotTables_Trimmed.xlsx, and FilterTable_Trimmed.bas.
Please feel free to use the application subject to the legal section at the bottom of this page.* I would love to know of any improvements you can suggest. Also, please let me know if something is not working. Be careful updating the criteria table. To add a column, type the header column in the cell next to the existing header row. When you press Return or Enter, the new column will appear an you can copy/paste into the TableBodyRange. To remove a column, delete the entire column.
UPDATE. It's actually much easier just to do an advanced autofilter with the criteria table (thanks, Debra of contextures.com). So, you can look at the codes as just examples of creating formulas for Table entries. It's a whole new way of thinking for me.
Return To Top
More Flow Chart
I have discovered that I would like a quick way to find out buttons are calling a particuler routine. When I do a Find in the VBA editor, the information on this list is not included. Therefore I have added a sheet to the flow chart. When filled, the sheet goes through the entire list of button calls and lists their procedure calls. The button to fill the list is on Tree2MM at the top of column G. Below are links to the updated codes. I have made all former references to get the latest version.
makeMMTree.bas, genProcedureFlowChart.bas, Globals4FlowChart.bas - Last updated 06/13/16. The new list does not treat duplicate button texts correctly. Different sheets have buttons with the same text but call different routines. A fix will be on its way soon. 06/25/16
Return To Top
List your iOS Apps in Excel
Long ago I found out how to make a PDF listing of my iOS apps. However, I really wanted to create an Excel spreadsheet of the list. The attached files show you how to do that on a Mac.
The method is to
1) Print the file list from iTunes to a PDF.
2) Export the resulting PDF to a Word file.
3) Clean up the Word File.
4) Save it as a UTF-8 text file.
5) Import the text file into Excel.
6) Clean up the spreadsheet.
The PDF gives you a full description of the above process. There are two VBA code files as well. DebugHelp.bas and the main code TheExcelCode.bas. I am including the former should you need to keep reloading the original text file and the latter creates the cleaned up Excel worksheet.
March 25, 2017