Data Validation with a ComboBox with AutoFill/AutoComplete

[MacroMan007]

I was wondering if there was any way to expand on using “Data Validation with a ComboBox using Named Ranges" (taken from www.contextures.com/xlDataVal11.html) and using a Dynamic Named Range where you can add items to the drop-down list if it isn’t already in the list. Say, for example, instead of having days of the week or months of the year; you have an ever growing list of Company’s names for your list.

I have downloaded the example from Contextures for Data Validation Combo box using Named Ranges and I also have a working macro of adding new names to a Dynamic list through some VB (taken from OzGrid (www.ozgrid.com/News/excel-dynamic-ranges-vba.htm).

Up until now, I have been using the VB adapted from OzGrid for adding names to the list, but now they are saying what good is this and they want to be able to type the name and have it AutoFill/AutoComplete the company name, but also want it to add the company name to the list if it isn’t already there. So, while each idea works beautifully on its own, they want it all if that is doable.

I suppose, for now, I could have the list on another sheet and have them add names manually there and use the ComboBox method until I can get it working. At least that way they won’t have to scroll through the list or manually type in the name every time slowing down their productivity, again if it is possible… but I have seen some amazing things done with Excel/Visual Basic.

Does anyone have any ideas how this could be accomplished?

Thank you in advance for your help,

Merry Christmas!

 

[Jerry Sullivan]

Hi Jay,

Combining those two concepts is a really good idea. Thank you for sharing the links.

Using the example provided in the "Data Validation with a ComboBox using Named Ranges" article, it is pretty easy to add the Dynamic Ranges. Instead of "DayList" referring to $A$1:$A$7, you make it refer to
=OFFSET(ValidationLists!$A$1,0,0,MATCH("*",ValidationLists!$A:$A,-1),1)

Now you have the ComboBox interface referencing a dynamic range. If your users manually added items to the bottom of the list, they would get added to the ComboBox dropdown.

The only part remaining in your wishlist is that if a user types in a new entry, you want the new item to get added to the dynamic range automatically.

Below is some code that will do that. Since it sounds like you might have a long list of company names, I added the option to re-sort the list when a new item is added.

'---Paste this into a Module
Option Explicit
 
Public Function Add_To_Dynamic_Range(strName As String, strItem As String, _
    blSort As Boolean) '---if not already in list, add with option to sort
 
    If Not Range(strName).Find(What:=strItem, _
        LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then Exit Function
    With Range(strName)
        .Resize(1, 1).Offset(.Rows.Count).Value = strItem
        If blSort Then
            With .Parent.Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range(strName).Resize(1, 1), _
                    SortOn:=xlSortOnValues, Order:=xlAscending, _
                    DataOption:=xlSortNormal
                .SetRange Range(strName)
                .Header = xlNo
                .Orientation = xlTopToBottom
                .Apply
            End With
        End If
    End With
End Function

To integrate this into the example you referenced, insert this code into the Worksheet_SelectionChange procedure below the existing line of code shown.

'---after this line in existing code...
   Set cboTemp = ws.OLEObjects("TempCombo") 
'---insert this:
   If cboTemp.ListFillRange <> "" Then
     Add_To_Dynamic_Range _
         strName:=cboTemp.ListFillRange, _
         strItem:=Range(cboTemp.LinkedCell).Value, _
         blSort:=True
 End If

Please let me know if this works for you. I appreciate the work of the two developers you reference...they did all the heavy-lifting on this one.

Merry Christmas to you too!

 

[MacroMan007]

Jerry,
Thanks for your reply. I hope you had a nice Christmas and I'll give your solution a try tomorrow and let you know how it works. I appreciate your help an awful lot.

 

[MacroMan007]

Hello Jerry,
I hope you had safe and Happy New Year celebration!

Well, I finally was able to find some time to test your solution and I'm getting an error. It's a Compile error that lands on xlSortOnValues and the error message is Variable not defined.

Note: This is in the code for Public Function Add_To_Dynamic_Range(strName As String, strItem As String, _
blSort As Boolean).

Was I supposed to modify the function or just paste it into a module, which is just what I did?

Thanks in advance for your assistance!

 

[Jerry Sullivan]

Hi Jay,

You should have been able to paste the first block of code into a code module as is. (The one that starts wtih: '---Paste this into a Module)

The second block ('---after this line in existing code...)
takes a bit of integration and that might be why the problem arose. My instructions assumed starting with the Contextures example you referenced and making the modification I described. Is that what you did?

I'll be glad to help you get this example up and running.

 

[MacroMan007]

Hello Jerry,
Yes, I did exactly as you said and I tried it in the Contextures example, not just my code and got the error in the Function code. I have a screen shot of the error, if you think that would help, but I'm not sure how to post that here.

Thanks again for your time.

 

[Jerry Sullivan]

Jay,

I've uploaded the file that I modified to a DropBox, if you want to compare this to your file and see what is different.

http://dl.dropbox.com/u/7603985/DataValComboboxSheet-Modified for Dynamic Range.xls

Please let me know what you find.

 

[renigar]

I just recently found this code on the contextures web site and it is working great for my needs but thought it might be even better for my purposes if the macro could be triggered initially on cell selection. I want it to trigger when I tab into the cell in question. I just don't know enough to do this since the second section of code already has Worksheet_SelectionChange. I'm not sure if it is possible to do or how to implement if it is. Any guidance would be greatly appreciated. I would post the code here but am having trouble pasting. I have some learning to do here. The code is still available at the contextures link at the top of the page.

Thanks,
renigar