Link to home
Start Free TrialLog in
Avatar of gilnari
gilnariFlag for United States of America

asked on

Limit Data Validation list

I have two worksheets, Worksheet A and Worksheet B.

In Worsheet B, I have two columns.  In the first column I have list of records all unique in value.  In the second column thy are have  value that defines a type of record for the first column and contains values like  R, N, P, T.

In Worksheet A, I want to create a Data Validation list from the records in Worksheet B where the values listed in first Column are equal to the value R from the second columns.

Also using MS Excel 2003

something like
=WKSheetB!$L$10:$L$439 where WKSHeeTB!$M$10:$M$439=R

I know this is no way close to right but any help be greatly appciated.
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image


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 instead, which is not officially an EE site, but is run by people connected to EE.

Avatar of gilnari


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.
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

Open 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

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
            'do nothing
        End If
        With cboTemp
            '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

Open 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.


Avatar of gilnari


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.

Avatar of dlmille
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gilnari


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