Sometimes getting multiple controls to talk nicely to each other in a Microsoft Access form can be difficult. In this example the goal is to create a form with a list box with multiple columns which the user will be able to select a company with and a combo box that will filter it’s results based on the selection in the listbox. The two controls will be used as a filter for a report that will be opened when the user clicks on a button on the form.

This is a quick example of how you can use the Dependents property of the Range object to highlight the dependencies of a cell when it’s changed. The idea is that when a cell is changed the code will show the cells that whose formulas depend on the value in the cell that has just been changed.

We’ve looked at VBA, Automation Add-ins and RTDs in previous posts. XLLs are significantly faster, and allow the developer to define the names/parameters of the functions. They’re also not that difficult to implement, although setting up the definitions of your functions can be a bit tricky. Unlike the other C++ add-ins, the XLL is not a COM server, it uses the Excel C API.

In a previous article I wrote about creating a buffer class in C# to access cells in a worksheet. The theory behind it was that accessing Excel one cell at a time is slow while doing it in batches is significantly faster. The same theory applies to all languages that you can program Excel in so in this article I present a class for VBA projects that does the same thing.

There are two ways to change cell values in an Excel spreadsheet: the wrong way and the fast way. The wrong way takes the form of a loop where each cell is individually accessed in a for loop. Each time you assign a value, your add-in makes a COM call into Excel. Calling COM object methods from C# or anything for that matter, is a slow process. It might be 10ms or so (just a guess) but if you were looping through 200k cells that becomes 2000 seconds of COM calls.

A common situation in Microsoft Office is the need to supply values from forms to queries as arguments. One way to do this is by referencing the form directly in the query. In the example below the criteria for ClientID would be [Forms]![View Invoice Form]![ClientIdList] which refers to a combo box on the "View Invoice Form".

Creating combo or list boxes are easy if the items in your lists don't change between revisions (for example, a list of months in the year). For most people, however, lists tend to be dynamic and in constant evolution. If you spend a lot of time updating each list, you'll inevitably forget to update one at a critical juncture, probably right before an important client presentation, knowing Murphy's Law! In order to automate a somewhat repetitive and manual task, as well as to minimize human error, you can use named ranges with formulas.

In this post, we will create a dynamic screening tool in Excel just by using formulas. To keep things simple, we won't use form controls; instead, we?ll use validation rules to provide users with drop-down lists.

The concept is to create a screening form where the user defines the parameters they want to screen by selecting ranges. These parameters are then inserted into formulas in a data sheet to indicate which data lines will match the criteria. The results are then displayed below the screening form using the match and index functions.