Unique drop down list option for each column


I am trying to make columns that have one unique option.

First check this image: https://i.imgur.com/uxwX57G.png

unique DDL option Column


Like you can see there is the option to take the circle icon in the DDL. 

But I want it only to be possible at 1 cell per column.

example: the circle icon is selected at the first row cell. When I select the circle icon in the third row. The first row cell gets empty.

Thanks for reading,

hope you can help me.

By: luka

One thought on “Unique drop down list option for each column

  1. Luka

    here is a solution:
    -Press ALT + F11
    -double click your sheet
    fill in this code and save it:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range, rng As Range, c As Long
    c = Target.Column
    Set rng = Range(Cells(3, c), Cells(14, c).End(xlUp))

    If Application.WorksheetFunction.CountIf(rng, 8) > 1 Then
    Application.EnableEvents = False
    For Each cel In rng
    If cel.Value = 8 Then
    cel.Value = “”
    Target.Value = 8
    End If
    Next cel
    End If

    Application.EnableEvents = True
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *