Protect List Validation

[Suresh1980]

Hello all,

If someone can help me out for the below issue that would be great.

I created list validation in "Column G", so that by using drop down we can select the name from the list.

All I want is to protect the "Column G", so that no one can manually enter the names other than the drop down list (Right now my team members copy and paste names from other cells. So list validation is getting lost as well as some type "Name A" or "NameA"). So when I try to run a Pivot I get multiple combination of "Name A".14207

If I use "Protect" option, its not allowing me use drop down option as well.

Is there an option to Protect the column and as well drop down can be used.

Regards
Suresh

[JKwan]

this will solve some of your problems, give it a try, this is a worksheet code!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ary As Variant

ary = Array("Name A", "Name B", "Name C", "Name D", "Name E")

If Not Intersect(Target, Range("F6:F100")) Is Nothing Then
With ActiveCell.Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(ary, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub

[Suresh1980]

Thanks for the code, let me try and get back to you incase of any questions

[Suresh1980]

Hi JKwan,

I think I haven't explained properly.

All I want is to protect the column G with a password as well use the drop down option.

Right now what happens is the column is protected with a password (123), but when I try to use the drop down it is not allowing me to insert the text from list.

Regards
Suresh

[Suresh1980]

Hi Team,

Kindly request some one to help me out please.

I have used different list validation for 4 columns approximately 300 rows for each column. All I want is to "restrict-paste-into-dropdown-cells".

Can someone help me out please.

Regards
Suresh