<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Part II - Drop Down List with Unique/Distinct Values (ComboBox, ListBox and Data Validation List Bonus!)

Published on
23,571 Points
6,771 Views
23 Endorsements
Last Modified:
Awarded
Editor's Choice
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!)
David Miller (dlmille)

Intro

This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Data Validation (DV) lists.
 
This is the Part II article, I promised, as a follow-on to Drop Down List with Unique/Distinct Values (Enhancing the ComboBox with a few steps and a little code) http:\A_5062.html,, where I've modified the code to support OLE Objects (Active-X Controls) that support lists (e.g., ComboBox and ListBox are the practical examples, today).  In addition, I've taken some of these learnings and applied them to a related Contextures tip http://www.contextures.com/xldataval10.html to greatly simplify the effort, for some, via enhanced Data Validation lists.

In each example – list object (ComboBox, ListBox) or DV list – I’ve leveraged the use of the dictionary (see matthewspatrick’s related article http:\A_3391.html) and a sort to deliver a unique list to these controls, based on an initial setup of a listfill range (or DV List range).

I’m not going to go into detail on the first part (ComboBox and ListBox) as the documentation in Part I of this Article, http:\A_5062.html, achieves that end.  Suffice it to say, I leveraged the same routines, “objectivizing” my variables such that either ComboBox or ListBox could be used interchangeably, based on preference.  I’ve also leveraged “late binding” for the Dictionary class, to ease deployment without the need to explicitly add the appropriate reference library.  The code that achieves this transformation is posted (along with demo spreadsheet add-in) at the bottom of this Article.

Data Validation (DV) Lists - Extended

The remainder of this write-up will focus on the Data Validation (DV) list, and how, with a few simple steps and a little code, we can dynamically deliver unique sorted lists for general use in any workbook spreadsheet.

How to install and use the Dynamic DV! Add-in.

At the bottom of this article, there’s a second posting for the Dynamic DV! Add-in.  With the add-in loaded, any data validation in any workbook that references a range for its reference list will have access to the features of Dynamic DV!  Note: Dynamic DV! depends on the Worksheet_Change() event, so if for some reasons events become disabled, just select the Dynamic DV! menu on the Add-ins tab and choose the Initialize option to reinitialize the event class for the tool, and re-enable Events.

1. Starting with a new/existing workbook

Let’s create a new workbook with a defined range of items that are not sorted in any particular order:
 Add a new/existing workbookOnce the blank workbook is created (or any existing workbook is opened), a click of the button on the worksheet initiates code to deposit a hidden temporary ComboBox on the active sheet.  Just hit OK.  Dynamic DV! Is now enabled on this worksheet.Note:  the hidden TempCombo ComboBox is made visible during use, and is loaded with data, dynamically, as needed.
 Initializing Dynamic DV!

2. Using new/existing DV Lists

With the new blank workbook created, we can then build our test range and data validation list.  I started a miscellaneous tracking list:  Customer, Products, Billing (etc…), selected the first open cell and built a DV list on the range I created:
 Assigning a range to the DV ListI shaded the DV cell grey to make it easier to identify, then copied it down to row 21.  For demonstration purposes, I’ve turned off the Add-in for the next comment.  Note the DV list shows the data in the range we created, unsorted and with duplicates:
 Simple DV list is unsorted and can have duplicates

3. Testing the Dynamic DV! Add-in

Now, let’s re-activate the Add-in and see what happens when we click on a DV cell:
 Dynamic DV! delivers a distinct, sorted list for the DV dropdownAnd that’s all there is to it!  Create as many DV Lists as you like, pointing to whatever valid ranges that exit, and Dynamic DV! will consistently deliver  distinct/unique, sorted list.
Note: there are two dropdown arrows in the above view.  One for the ComboBox, and one for the DV List.  It’s simple enough to eliminate the DV list’s dropdown arrow, by de-selecting it in the Data Validation toolbar:
 De-select the in-cell dropdown to eliminate the double-dropdown view
How it works

When selections are made on the worksheet, a Worksheet_Change() event is triggered.  The cell is then interrogated to determine if it is a data validation cell with a list and a valid range.  The temporary ComboBox object TempCombo is then loaded with a unique, sorted list of items from that range, using the previously discussed (see Part I: http:\A_5062.html) sub loadMyListObjectUnique().

Here’s the code for trapping that event and delivering the DV list in the fashion described, which is stored in the ThisWorkbook codepage (see below notes for add-in):

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call DataValidationManagement(Target)
End Sub

Open in new window

And here’s the DataValidationManagement() routine stored in a public module:
Sub DataValidationManagement(ByVal Target As Range)
'The below called from "change" events associated with Data Validation Lists
'Original Concept for Data Validation/ComboBox drop down from http://www.contextures.com/xldataval10.html

Dim str As String
Dim cboTemp As OLEObject
Dim WS As Worksheet
Dim vType As Variant
Dim chkDVList As Variant
Dim proceedSetup As Boolean


    Set WS = ActiveSheet
    
    On Error Resume Next 'connect to temporary ComboBox "TempCombo", testing along the way using Err.Number
    
    Set cboTemp = ActiveSheet.OLEObjects("TempCombo")
    If Err.Number <> 0 Then 'the ComboBox object must have been inadvertently deleted, so let's create it
        Set cboTemp = ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1")
        cboTemp.Name = "TempCombo"
    End If
    
    proceedSetup = False
    vType = Target.Validation.Type 'Empty if there is no validation type at Target address
    If Left(Target.Validation.Formula1, 1) = "=" Then 'check to see if there's a validation reference to a range address
        proceedSetup = True
    End If
    
    On Error GoTo 0
    
    On Error GoTo errHandler 'fall to bottom on any errors
      
    If Not IsEmpty(vType) And vType = 3 And proceedSetup Then 'Target.Validation.Type = 3 Then 'if the cell contains a data validation list
        Application.EnableEvents = False
                        
        'get the data validation formula
        str = Target.Validation.Formula1
        
        chkDVList = Evaluate(str)
        
        str = Right(str, Len(str) - 1)
        
        If Not IsError(chkDVList) Then 'must be a range reference in the Data Validation list; else just use regular data validation, then
        
            
            With cboTemp
                'show the combobox with the list
                .Visible = True
                .Left = Target.Left
                .Top = Target.Top
                .Width = Target.Width + 5
                .Height = Target.Height + 5
                .ListFillRange = "'" & Evaluate(str).Parent.Name & "'!" & Evaluate(str).Address ' modded
                .LinkedCell = Target.Address
            End With
            
            Call loadMyListObjectUnique(cboTemp, True, False)
        
        Else
            'do nothing
        End If
    Else
        With cboTemp
            'hide the combobox, and get it out of the way from inadvertent deletion
            If .Visible = True Then
                .Visible = False
                .Left = Range("BB5000").Left
                .Top = Range("BB5000").Top
            End If
        End With
    End If
        
errHandler: 'attempt to re-initialize application-level events on error
    'Call InitalizeAppEvents 'not needed if this is called from regular events
    Application.EnableEvents = True
    Exit Sub
End Sub

Open in new window


As previously mentioned, and noted in the code, I started with an excellent code snippet from Contextures http://www.contextures.com/xldataval10.html, that instantiates a the TempCombo ComboBox on top of a DV list, when the relevant cell is selected.  The value I added with this article is ensuring the ComboBox dropdown is populated with a unique/distinct, sorted list.

Attachment I:  Attached, please find an all-in-one demonstration workbook that contains the entire codeset, supporting dynamic dropdown lists through list objects (e.g., ListBox, ComboBox) and DV lists.  The example workbook has several examples for you to discover opportunities for your next project:
Demo Workbook for dynamic listObjects DV list evaluationDynamic-DV---list-objects-and-DV.xlsm
Attachment II v2.0:  Attached, please find the Dynamic DV! Add-In to easily add this functionality to any existing project with a click of a button.  Also attached, please find the Demo spreadsheet used in this article.  You can start using Dynamic DV! today:
DynamicDV-r10v2.zip
Thanks to Feedback from others, especially Jfiestere and Bright01, I've incorporated their constructive feedback into these enhancements.

Enhancements include:

1.  More control as you navigate your spreadsheet.  I add a comboBox event handler to support TAB, SHIFT-TAB, ENTER, SHIFT-ENTER, SHIFT-UP arrow, and SHIFT-DOWN arrow navigation, just like with normal Excel spreadsheet handling.  The app won't STOP on a data validation range when navigating over one, unless YOU stop.
2. AutoFill bug fixed, now you can start typing and see the text autofill.
3. If you're editing a word in DynamicDV! and hit ESC, the prior value reverts to the cell
4. Added Data Validation checking (yes, it needed it;) with a prompt if the value entered is not in the list (careful, if you DV list includes a blank cell, it will never alert, even with normal DV!)
5. Note, when you are backspacing on a word in the combo box, the cells are highlighted which is a difference from an Excel normal cell or DV cell.  I haven't modified this "feature", as you can just hit the delete key when you want the word to be truncated.

I also added an auto install function for the add-in.  With Excel already started, just download the new add-in version 2.0 and run it, after a couple prompts, it should be installed.

There is a revised Add-in menu for DynamicDV! that is fairly self explanatory.  You can turn it on/off for your current Excel session, and you can toggle the startup state (e.g., when you start Excel whether Dynamic DV is automatically on or off.)  Finally, I also added a ToggleValidationAlert to turn on/off the validation "feature".

Attachment III: Original Dynamic DV!
Dynamic-DV---DV-list-addin.xla
Attachment IV:Demo example workbook used in this article.
Dynamic-Lists---Demo-Example.xls

Enjoy!

David Miller (dlmille)
23
Comment
Author:dlmille
  • 5
  • 4
  • 2
  • +2
13 Comments
LVL 61

Expert Comment

by:Kevin Cross
Re: http://www.experts-exchange.com/A_5062.html#c29205
Thanks and I voted YES here too. Appreciated as much as part I.
0
LVL 85

Expert Comment

by:Rory Archibald
Nice follow-up article! I've actually just linked to it in another forum, though sadly I don't suppose you get any points for that.

Edit: It seems you get points for views now, so maybe it will help after all.
Rory
0
LVL 42

Author Comment

by:dlmille
Thanks, Rory - what's the forum?

Dave
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

LVL 85

Expert Comment

by:Rory Archibald
OzGrid (not as busy as it used to be, but may generate a few hits as it still turns up in searches!)
0
LVL 42

Author Comment

by:dlmille
Thanks - can you post the link here :)  I wanted to check it out.

Dave
0
LVL 85

Expert Comment

by:Rory Archibald
Post was here: http://www.ozgrid.com/forum/showthread.php?t=155482
Not precisely the main focus of the question, but I thought it would be extremely useful to anyone with lists that large. :)
0
LVL 10

Expert Comment

by:broro183
Looks good Dave,

I can see there are a few tricks in there such as the App' level events that I may "borrow" ;-)
There a few bits that could be tidied, for example, "set WS = activesheet" is included but then the ws variable is never used in one of the subs.


You know how we joked in Part I about the need (or lack of) for using Double in one of the Subs, well... & here I go, off on a slight/complete tangent!

ColinLegg [URL="http://www.excelforum.com/2524846-post12.html"]taught me something recently[/URL] that I hadn't been aware of regarding the Typing for cell values - try this out...
I never knew that about cells containing numerical values being identified as Doubles, but I was able to support it pretty quickly via the ImmediatePane with the below statements
?typename(activecell.Value2)
' = Double 'where the active cell contained 1
?typename(1)
' = Integer
?typename(32000)
' = Integer
'& we can see that some internal conversion does occur because...
?typename(33000)
' = Double

Open in new window

see http://www.excelforum.com/2526000-post16.html


Rob
0
LVL 85

Expert Comment

by:Rory Archibald
Colin (aka The Beard) is weird though. Any sane person would have used a Long and left the coercion to a Double for Excel to handle at the end. :)
0
LVL 10

Expert Comment

by:broro183
*Chuckle*
I'm pleased to hear that Rory.
I have stored the "stored as Double" fact in the ol' memory bank but at this stage I'm only using double when I need to have decimal places pushed through to the spreadsheet.
0
LVL 42

Author Comment

by:dlmille
Thanks for your comments.  I almost always set something to the active sheet I'm working with (and almost always use that variable as well, hah)!

Well, I'm finally off my DV list / ListObjects "kick".  I've used this particular set of tips quite a few times in the past months with posted questions, and now I can finally have a place to point to without needing to restate directions.  Thanks to participating on E-E, I've developed a few other tools in other dimensions, I hope to share, in the coming months...

PS - For more on setting up Application-level events, go to Chip Pearson's site (where I learned the tips):  http://www.cpearson.com/excel/appevent.aspx

Cheers,

Dave
0
LVL 18

Expert Comment

by:WaterStreet

Voted Yes at the top of the article.
0
LVL 42

Author Comment

by:dlmille
Crystal :). Ill be amending.  Tks
0
LVL 42

Author Comment

by:dlmille
Note to all:  Please see the updated DynamicDV! utility, which comes with a few new features.

Enhancements include:


1.  More control as you navigate your spreadsheet.  I add a comboBox event handler to support TAB, SHIFT-TAB, ENTER, SHIFT-ENTER, SHIFT-UP arrow, and SHIFT-DOWN arrow navigation, just like with normal Excel spreadsheet handling.  The app won't STOP on a data validation range when navigating over one, unless YOU stop.
2. AutoFill bug fixed, now you can start typing and see the text autofill.
3. If you're editing a word in DynamicDV! and hit ESC, the prior value reverts to the cell
4. Added Data Validation checking (yes, it needed it;) with a prompt if the value entered is not in the list (careful, if you DV list includes a blank cell, it will never alert, even with normal DV!)
5. Note, when you are backspacing on a word in the combo box, the cells are highlighted which is a difference from an Excel normal cell or DV cell.  I haven't modified this "feature", as you can just hit the delete key when you want the word to be truncated.


I also added an auto install function for the add-in.  With Excel already started, just download the new add-in version 2.0 and run it, after a couple prompts, it should be installed.

There is a revised Add-in menu for DynamicDV! that is fairly self explanatory.  You can turn it on/off for your current Excel session, and you can toggle the startup state (e.g., when you start Excel whether Dynamic DV is automatically on or off.)  Finally, I also added a ToggleValidationAlert to turn on/off the validation "feature".

Download Attachment II v2.0 to use the latest.

Cheers,

Dave
0

Featured Post

Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Join & Write a Comment

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month