• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to prevent user selecting a duplicate value from a drop down list.

Hi

I cant' find this solution anywhere on google.

I have a colum where a user select a value from a list.

I want the excel to prevent the user from sleecting the same choice twice .


tab: Testing_Main - cell: m10, m11,, etc. currently the user could select TEST_1 twice... i need to prevent this.

thanks

David
 

Attachments

  • SOX_Testing_Template_Generic__Final_V12.0.xlsm
    301.2 KB · Views: 8
One approach might be to use code in the worksheet change event for sheet Testing_Main to detect if the choice has been selected already.

Example
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim N As Long, rng As Range
    If Target.Cells.Count = 1 And Not Application.Intersect(Target, Me.Columns("M")) Is Nothing Then
        Set rng = Me.Range("M10", Me.Range("M" & Me.Rows.Count).End(xlUp))
        N = Application.WorksheetFunction.CountIf(rng, Target.Value)
        If N > 1 Then
            MsgBox Target.Value & " has already been chosen. Please choose another."
            Application.EnableEvents = False
            Target.Value = ""
            Application.EnableEvents = True
        End If
    End If
End Sub
 
Back
Top