Autocomplete Workaround for Validation Dropdowns

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

print
As might know there is no autocomplete functionality with Data Validation dropdowns.  I wonder if the Microsoft development teams are considering this as it would be a wonderful feature.  I sometimes wonder if the validation dropdown was an accidental dropdown feature from Microsoft – the intent was for enforcing validated data but of course users use it as automated data entry with consistent values as a side benefit.  Maybe this is why it’s still quite basic.

Anyway on a client’s project I worked on today they really needed this feature.  The dropdown list was 80 items long and the values could have 3 or more sylables.  So the only way is to use some arrangement with a combobox control which has that autocomplete built in feature.  Tricky as you don’t want 80 comboboxes going down a page (too tedious to maintian for lots of reasons) you need some VBA to make this happen somehow.

Combobox PropertiesI came across Debra Dalgleish’s wonderfully billiant solution in her fantastic Contextures site here Data Validation Combo Box using Named Ranges , man is that a great solution!  Debra has a link also for downloading the workbook example as well.

I am not going to repeat it all because it’s all there on Debra’s site.

But I will explain a hitch I had with it.  Basically you insert a Control X Combo box and ensure the autocomplete MatchEntry Property for the control along with how many entries you want to appear in list when it is triggered.  A double click in a data validation field and the VBA is smart enough to pick up the validation range definition and a floating (invisible otherwise) combobox appears with you validation list appears in larger font, with the number of rows you defined and with autocomplete capability.  

Now the code didn’t work for me at first.  After a half hour of frustration the penny dropped it was because my dropdown was in a Table.  This in itself wasn’t the problem, but what was though was my rangenames for the data validation lists were created using structured references, not simple range references.  I did this to take advantage of the dynamic nature of Tables ie add a row to the Table automatically adds to the range name reference.

So to use this workaround, I had to change my range name reference to a dynamic range reference which I often used a couple of years before getting into using Tables.  My list of names was in Sheet 1 in a Table called Names which was in Column C starting in row 12 and has 9 non blank entries before it starts.

If you went to the Range Name Names using Structured References:
=tblSheet1[Names]     this is the Table Name tblSheet1 and the Column heading Names

Range Name Names2 using dynamic range references:
=’Sheet1′!C$12:INDEX(‘Sheet1’!$C:$C,COUNTA(‘Sheet1’!$C:$C)+9)

So I had to create Names2 racking my memory for this dynamic formula that I used a lot a few years back before using Tables now so often instead for dynamic range names,  I had forgotten it.

Then if this Range Name is used in the data validation formula in the data validation dialog ie =Names2,  Debra’s event BA code works beautifully on the double click.  The only other thing to say is if you want to find the Combobox control later to change a setting say to change from 15 to show in the list to 20,then it’s hard to find as the code has made it invisible. 

I used this method but there is probably better using VBA in the immediate window – on the worksheet Ribbon I choose Developer and Design Mode.  Then used F5 Goto Special then Objects this highlighted all controls including the Combobox.  I had to then widen it.  Then separately select it again and right click for properties.

After I had wrote thie Debra kindly responded to an email I had sent earlier on re this problem and as always she was onto this issue and here is a link on her take on this issue and another possible solution.  Debra sells a Pro version of this technique for a modest $20 the last time I looked which would also be well worth considering.

 

 

Tagged with: , , , , ,