Click here to Skip to main content
14,664,862 members
Rate this:
Please Sign up or sign in to vote.
See more:
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then
With Target.Validation
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, _
 Operator:=xlEqual, Formula1:="=Sheet1!S1:S22"
End With

ElseIf Target.Column = 3 Then
With Target.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, _
Operator:=xlEqual, Formula1:="=Sheet1!T1:T22"
End With
End If

End Sub


What I have tried:

Here I'm just trying to get a data validations list using vba, but still can't execute
Posted
Updated 4-Aug-16 21:59pm
v2
Comments
Richard MacCutchan 5-Aug-16 2:57am
   
What is the error message and where does it occur?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

It's really easy to resolve your issue...

As far as i remember, you can't add validation object if it already exists. You have to remove existing validation to be able to add new.
See: Validation.Add Method (Excel)[^]

For further information about Application defined or object ... error, please check this: Application-defined or object-defined error[^]

Finally, i'd suggest to add error handler to your code. How? Handle Run-Time Errors in VBA[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100