Limit Data Validation list

gilnari,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick

I have attached a very very water down version of what I am trying to accomplish.

In the First worksheet Tet I have a list of Comnpanies and I want to choose from the drop down list in Column B only those items that have analysis of R from worksheet B.  This is very water downed as the second worksheet has thousand of records so setting up independt list to referent may not be an option.
Figure-Out-how-to-Filter-a-Named.xls

I've created a solution, leveraging some techniques I talk about in a recent article, http:\A_6429.html.  I've culled the code back to the minimum needed to screen out all from the Analysis range, based on the Criteria "R" offset exactly 1 column to the right.  Should you wish to, I could add additional code to return a sorted list.  However, I've kept this solution as simple as possible.

Based on a worksheet change event, the application drops a populated combobox down on top of any validation list - in this case, the application first screens the data validation formula =Analysis (one column to the right) for the value "R", then adds the items to the combobox.  You can use the combobox just as you would use the DV list, but with the combobox, we have a bit more flexibility as we can load it based on these rules.

Here's the code that goes in the ThisWorkbook codepage (hit alt-F11 and paste this in ThisWorkbook):
 

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Uncomment the below code and comment out the code in Combo Box and DV Lists, if you only want this feature on the current sheet

    Call DataValidationManagement(Target)
    
End Sub

Select allOpen in new window

This routine calls a subroutine called DataValidationManagement() which does most of the work - this code is pasted in a public module:

 

Option Explicit
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 vType As Variant
Dim chkDVList As Variant
Dim proceedSetup As Boolean
Dim myCell As Range

   
    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
            'load the DV List based on the "R" criteria, using cboTemp comboBox
            
            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 = ""
                .LinkedCell = Target.Address
            End With
        
            'traverse the data validation list, looking one column to the right for the ("R") to see what gets added
            For Each myCell In Range("'" & Evaluate(str).Parent.Name & "'!" & Evaluate(str).Address)
                If myCell.Offset(0, 1).Value = "R" Then
                    cboTemp.Object.AddItem myCell.Value
                End If
            Next myCell
        Else
            'do nothing
        End If
    Else
        With cboTemp
            '.Clear
            '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: 're-enable events
    Application.EnableEvents = True
    Exit Sub
End Sub

Select allOpen in new window

As you can see in this code, we drop a combobox ("TempCombo") on top of the data validation cell, then we test the data validation list (one column to the right) for the "R" key.  If found, we add it to the combobox.

See attached demo worksheet.  Just click in the data validation cells, and you'll see the drop-down now meets your criteria.  Note, this function will persist for ALL data validation lists in the workbook.  You can narrow this to a specific sheet by adding a line to the ThisWorkbook Code as follows:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Uncomment the below code and comment out the code in Combo Box and DV Lists, if you only want this feature on the current sheet

    If Sh.Name = "SpecialValidation" Then 'just change "SpecialValidation" to the sheet name where you want this special function to exist
        Call DataValidationManagement(Target)
    End If
   
End Sub


Please advise if more assistance is required.

Enjoy!

Dave
Figure-Out-how-to-Filter-a-Named.xls

Hi Dave,

Yup got it mostly to work and was able to expand on it.  However one minor issue the records to chose from the list repeant and each time you add a new record to to Test and choose a value from the list it seems to repeat iteslf.  Almost seems for each new record there needs to be a rebuild of the combobox.  Any ideas?

Yes, the combobox instantiates itself and refreshes based on the data validation formula each time.  That's the design.  I could provide a more customized version, if theres a noticeable delay you're dealing with.

Dave

Thank you very much, you have no idea how this saved me.