XL Data Validation: Remove Selected Values From Option List? 1

[VBAjedi]

Hi all!

I have an Excel "data" range that uses data validation to make sure entries match the values in a "key" range. I need to set it up so that if I select a value from the validation drop-down list in a data cell, that value is no longer an option in the other validation drop-downs in that data range.

For example, say my key range contains values "Frank" and "Joe". If I select "Frank" in a cell of my data range, I want to remove "Frank" from the data validation drop-downs in the rest of the data range.

How can I go about this?

Many thanks in advance!

VBAjedi

 

[SkipVought]

VBAjedi,

Create a procedure in a module...

Public bChange As Boolean
Sub DeleteNameFromList(rng As Range)
    For Each r In [NameList]
        If r.Value = rng.Value Then
            r.Delete shift:=xlUp
            Exit For
        End If
    Next
End Sub

In the worksheet change event create...

Private Sub Worksheet_Change(ByVal Target As Range)
    If bChange Then Exit Sub
    bChange = True
    DeleteNameFromList Target
    bChange = False
End Sub

The change event will call the DeleteNameFromList procedure. You might want to have some code to check if the Target range intersects with your validation range.

hope this helps Skip,
Skip@theofficeexperts.com

 

[VBAjedi]

Skip,

I think that's a good start. However, my "key" list needs to be used over and over again - so I can't really delete rows from it! If the user changes their mind and puts Joe in a cell to replace Frank, then Frank needs to be available again in the other data cells drop-down lists.

A bit complicated, huh!

VBAjedi

 

[SkipVought]

Jedi,

You have something beyond simple data validation. You also need code to prevent duplicates from being entered in the list. You could use a ComboBox from the Toolbox and load the list from the master. Then remove entries from the list as they are selected.

The ComboBox can be programed to be made Visible in the range of data validation just like the Data/Validation dropdown.

Hope this helps Skip,
Skip@theofficeexperts.com

 

[VBAjedi]

Skip,

I should have known . . . nothing I attempt ends up having a simple solution! :^) Oh, well. I'll give your suggestion a shot.

Do you have a code snippet illustrating how to program a ComboBox to be made visible like the data validation box? Say for the sake of example that my data range is A4:E30 .

Thanks again!

VBAjedi

 

[SkipVought]

First you need to place a Combobox on you sheet.
Here it is...

Private Sub ComboBox1_Click()
    With ComboBox1
        .Visible = False
        ActiveCell.Value = .Text
        .RemoveItem .ListIndex
    End With
End Sub

Private Sub Worksheet_Activate()
    ComboBox1.Clear
    For Each r In [YourValidationList]
        ComboBox1.AddItem r.Value
    Next
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngInter As Range
    Set rngInter = Intersect(Target, Range("A4:E30"))
    If Not rngInter Is Nothing Then
        With ComboBox1
            .Visible = True
            .Top = Target.Top
            .Left = Target.Left
            .Width = Target.Width
            .Height = Target.Height
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

Skip,
Skip@theofficeexperts.com

 

[VBAjedi]

Skip,

Here's what I ended up doing. A star for your contributions!

I used your code, but beefed up the Selection_Change routine. It now populates the Combobox on the fly (using the entire key list), then removes any values that already appear in the current data column. This way my users can only select a key value once for each column (it's a resource scheduling utility where each column represents one month). With this approach the item becomes available again if the user deletes it from the column. It works beautifully!

Here's my code:

Private Sub ComboBox1_Click()
    With ComboBox1
        .Visible = False
        ActiveCell.Value = .Text
    End With
End Sub

Private Sub Worksheet_Activate()
    ComboBox1.Visible = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim rngInter As Range
    Dim r
    Dim x, Item
    Set rngInter = Intersect(Target, Range("F6:AD10000"))
    If Not rngInter Is Nothing Then ' Selected cell is in my data range

        ' Populate ComboBox with values from my key range
        ComboBox1.Clear
        For Each r In Sheets("Rotate Contracts").Range("A6:A5000")
            If Len(r.Value) > 0 Then
                ComboBox1.AddItem r.Value
            End If
        Next
        ' Remove values already used in the current column of the data range
        For Each r In Sheets("Rotates Planner").Range(Cells(6, Target.Column), Cells(5000, Target.Column))
            If Len(r.Value) > 0 Then
                x = -1
                For Each Item In ComboBox1.List
                x = x + 1
                If Item = r.Value Then
                    ComboBox1.RemoveItem x
                End If
                Next Item
            End If
        Next
        ' Display Combobox at currently selected data cell
        With ComboBox1
            .Visible = True
            .Top = Target.Top
            .Left = Target.Left
            .Width = Target.Width
            .Height = Target.Height
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

Thanks again!

VBAjedi