Simple filter for Data Validation Drop-Down List

Negger

New Member
Joined
Jul 18, 2013
Messages
9
I have a two sheet workbook, sheet two containing a database saved in a table (exported from Access) and sheet one being my form that auto-populates most of the cells via a drop-down data validation list from the first column of the table.
See previous post :
http://www.mrexcel.com/forum/excel-questions/718097-nest-vlookup-isblank.html#post3538302
The drop-down contains approximately 1,000 items, referencing records in the table, each in a numeric format 1234/123, of which there are around 15 unique pre-fix's, (1234)
As this is quite unwieldly in an 8 row drop-down, ideally I would like to place another drop-down at the top of my form sheet containing the 15 unique four figure prefix's that would then filter the previous drop down to show only the 65 records with the same prefix.
I have found a method here:
Shorten Data Validation List With Excel Filter Macro | Contextures Blog
but this seems far more complicated (?) than I need.
Can anyone suggest a simpler method of achieving this filter, bearing in mind that I need to circulate the workbook to others who are even less Exel literate than myself, so needs to be idiot proof !
Many Thanks
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I have a two sheet workbook, sheet two containing a database saved in a table (exported from Access) and sheet one being my form that auto-populates most of the cells via a drop-down data validation list from the first column of the table.
See previous post :
http://www.mrexcel.com/forum/excel-questions/718097-nest-vlookup-isblank.html#post3538302
The drop-down contains approximately 1,000 items, referencing records in the table, each in a numeric format 1234/123, of which there are around 15 unique pre-fix's, (1234)
As this is quite unwieldly in an 8 row drop-down, ideally I would like to place another drop-down at the top of my form sheet containing the 15 unique four figure prefix's that would then filter the previous drop down to show only the 65 records with the same prefix.
I have found a method here:
Shorten Data Validation List With Excel Filter Macro | Contextures Blog
but this seems far more complicated (?) than I need.
Can anyone suggest a simpler method of achieving this filter, bearing in mind that I need to circulate the workbook to others who are even less Exel literate than myself, so needs to be idiot proof !
Many Thanks

I have a suggestion for smart data validation. Why don't you insert an active X combo box rather than the normal combo box. These are the advantages:1. You can select the number or rows visible ListRow property. 2. You can select auto-complete from the first letter MatchEntry property: select match from the first letter. This way as soon as the user start typing he will automatically narrow the options. Regards, Mario
 
Upvote 0
Dear Marious
Thank you for your reply. I've found a method here:
Excel Data Validation Combo box using Named Ranges
but haven't managed to get it working yet! However, my impression is is that the combo itself is side-lined and that each individual data-validation drop down (dvdd) will get filtered once I start to enter data in it ?
Ideally I would like the combo to be "active" i.e. if I select "1234" in that combo, only those records starting "1234" will be available in the dvdd, enabling "batch" entry via a conventional dvdd i.e. without having to double click each individual box and then enter 1,2 -3-4 each time.
Once i get the method to work I will report back.
Thanks Again.
 
Upvote 0
I haven't managed to get this code to work yet. I've played around with replacing sheet names, named ranges etc. but still no joy.
When I double click on a data validation drop down list a new drop down box appears "over-top" of the dvdd, which I presume is the active X combo ?, however it is blank ! I get no error messages etc ?
The Input Sheet is called "Alms" and the data sheet containing the table with the data validation list at Column A is called "GADData"
The table is called "GAD Table" & the data validation list in Column A is called "GAD"
The active X combo is called "DataValidationCombo"
The code is :
'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("GADData")

Set cboTemp = ws.OLEObjects("DataValidationCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Cancel = True
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
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 = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.DataValidationCombo.DropDown

End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("DataValidationCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp

Private Sub_KeyDown(ByVal KeyCode As MSForms. ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
'====================================
The error checker doesn't like the line in red, but that shouldn't be causing the problem ?
If anyone can spot what the problem is, assistance would be much appreciated.
Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top