Simple filter for Data Validation Drop-Down List

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Simple filter for Data Validation Drop-Down List

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Simple filter for Data Validation Drop-Down List

     
    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-q...ml#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

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Location
    Houston
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple filter for Data Validation Drop-Down List

    Quote Originally Posted by Negger View Post
    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-q...ml#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

  3. #3
    New Member
    Join Date
    Jul 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple filter for Data Validation Drop-Down List

    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.

  4. #4
    New Member
    Join Date
    Jul 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple filter for Data Validation Drop-Down List

      
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

  

 

 

DMCA.com