Add a check of which WS you are working. That should give you an idea

Sub AddValidation ()
msgbox Range("D1").parent.name
If Range("D1") < "" Then

NickHK

wrote in message
oups.com...
Here is the code. Now when I change "A1" it calls the Sub
AddValidation and works fine, But when I click CommandButton1 it calls
the Sub ChangeRange which changes ("A1") which in turn calls Sub
AddValidation and that is when I get the error on the .Add Type line
in the Sub AddValidation. My code is much to much to post here so I
did this code (which is the same thing on a much smaller scale) and
got the same result . Any input would be awsome.
__________________________________________________ ___
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
Call AddValidation
Else
Exit Sub

End Sub
__________________________________________________ ___

Private Sub CommandButton1_Click()

Call ChangeRange

End Sub
__________________________________________________ ____
Sub AddValidation ()

If Range("D1") < "" Then
With rOpt1Choices.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=Range("D1")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid Entry"
.InputMessage = ""
.ErrorMessage = "Please make a choice from the drop down
list"
.ShowInput = True
.ShowError = True
End With
End If

End Sub

_________________________________________
Sub ChangeRange ()

Range("A1") = Range("B1")

End Sub
_________________________________________