Posts Tagged ‘combo box’

h1

Developer Tabs and Drop Down Lists in Word 2007

17/03/2009

It seems that drop down lists are the in thing at work right about now.  One of my colleagues dropped by with a query about creating drop down lists in Word 2007.  So this is how to go about doing it…

First of all you need to ensure that the Developer tab is enabled in Word.  To do this you need to click on the Microsoft Office button (top left) and then on the Word options button at the bottom of the office menu that appears.  In the Popular section there is a list of check boxes about the “Top options for working with Word”.  One of these check boxes is called “Show developer tab in the ribbon” … this is the one that needs to be ticked.

With the developer tab enabled and selected the controls section on the ribbon is where we need to focus our attention.  The fourth button across on the top line is the one required to create the drop down list control – a.k.a. a combo box.  Clicking this button inserts a combo box at the current cursor position.

The next step is to fully define the combo box.  By default it has one entry in it – “Choose an item”.  Select the combo box and then select properties from the controls section on the ribbon.  Alternately you can enter design mode (from the controls section on the ribbon) and right click to get to the combo box properties.

The properties dialog provides a quick and easy way to not only add and remove items from the combo box’s list of selectable items, but also to name it something meaningful using the title.

Once you’re done editing the document, you then make it into a form by selecting “protect document” on the protect group on the developer ribbon and then selecting “Filling in forms” from the editing restrictions option.

The problem however seems to be that when you set the protection options in the properties of the combo box you can stop it from being deleted, you can stop it from being edited (at all!), but you can’t seem to stop it from having an option overtyped.  Seems a little bit odd and this is my one outstanding issue with the control.

Advertisements
h1

Excel – Special Names in Dynamic Lists

15/03/2009

I posted earlier about a better way for producing dynamic lists in Excel.  One of the “tricks” I used to simplify things was using the text values of the items in a primary list to identify a secondary list to use.

i.e.

Define the drop down lists

Define the drop down lists

Here We have three lists with the options set as named ranges called “Type”, “Fruit” and “Vegetable”.  Using data validation, drop down lists and Excel’s INDIRECT function means that if “Vegetable” is selected from a drop down linked to the “Type” list as a primary list, a related secondary list drop down will dynamically link to the “Vegetable” named range to validate the drop down.

This eliminates the need to do any complex lookups, but complex lookups look like the option when confronted with a primary list that contains entriess that would be invalid as names for a named range.

For example if we were looking at say UK regions and cities then an entry of “North Yorkshire” in the primary list would cause an issue as it contains a space which is an invalid character in an Excel name.

My solution is to replace the space in the naming of the range with an underscore.  This obviously won’t match to the entry in the primary list but we can use a quick substitution to accommodate this.

Simply put we might have something like in the data validation list entry for a cell linked to cell A2:

=INDIRECT(SUBSTITUTE(A2,” “,”_”))

This would simply replace the spaces with underscores and provide the valid name for Excel to use.

If we’re using the full validation option from my previous post on data validation, then this substitute would be moved into the function of the working area.  So using the same example as the previous post:

Cell D4 would now contain the following function:

=IF(B4=””,””,SUBSTITUTE(B4,” “,”_”))

Cell C4 would (still) contain the following for its data validation list source:

=INDIRECT(D4)

So we can still avoid complicated lookup functions and still keep things relatively simple and instantly readable.

h1

Excel – Dynamic Drop Down Lists with Full Validation

15/03/2009

This post has now been migrated to ThoughtAsylum.com.

Follow this link to go directly to the article.