I have macro that sets up a validation list on a cell. When the macro
is called by the Worksheet_Change event it works fine, but when I call
the macro from another macro in a standard module I get Run-time error
'1004' Application-Defined or Object-Defined error. Is there anyone
that can explain this to me, I am in diar need.

Thanks
Kyle Kelsch

Can we see the code?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
oups.com...
I have macro that sets up a validation list on a cell. When the macro
is called by the Worksheet_Change event it works fine, but when I call
the macro from another macro in a standard module I get Run-time error
'1004' Application-Defined or Object-Defined error. Is there anyone
that can explain this to me, I am in diar need.

Thanks
Kyle Kelsch

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
_________________________________________

I really need this to work for me and am hoping for a response so I am
going to keep posting to this until I get a response.
Great and many thanks to anyone who has any input or work arounds or
anything.

FIrst, you're missing an END IF in this code

Private Sub Worksheet_Change(ByVal Target As Range)

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

End Sub

--- Next problem

Sub AddValidation ()

If Range("D1") < "" Then
With rOpt1Choices.Validation '<~~~what is rOpt1Choices?
..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


" wrote:

I really need this to work for me and am hoping for a response so I am
going to keep posting to this until I get a response.
Great and many thanks to anyone who has any input or work arounds or
anything.

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
_________________________________________

BTW, it's about 10 PM where I am, so I probably won't be around much longer
to assist.

" wrote:

I really need this to work for me and am hoping for a response so I am
going to keep posting to this until I get a response.
Great and many thanks to anyone who has any input or work arounds or
anything.

Yeah I did leave out the End If in my post, however my code in my
workbook does have it. Also as I stated before, this is just a very
small version of what I really have so as for the rOpt1Choices I meant
to have that say Range("C1") which I also have in my workbook.

I put the above code in a new workbook with the correct changes and I
still get the error. I think it has something to do with the command
button because it only gives me a problem when I start the macro with
the command button. As for the check of which worksheet I am on , I
can give it a try but am not very hopeful.

Thanks for the input. And if there is anyone else that would like to
give a stab at it I would greatly appreciate it.

Hi Kyle,

Please would you post the problematic code?


---
Regards,
Norman


wrote in message
oups.com...
I have macro that sets up a validation list on a cell. When the macro
is called by the Worksheet_Change event it works fine, but when I call
the macro from another macro in a standard module I get Run-time error
'1004' Application-Defined or Object-Defined error. Is there anyone
that can explain this to me, I am in diar need.

Thanks
Kyle Kelsch

It would help if you'd tell us what line it stops on and the error message.
You may want to add a bunch of debug.prints. You may be referring to the
wrong worksheet. Try entering something like
debug.print range("D1").parent.name
to find out what worksheet it thinks it's referring to.


" wrote:

Yeah I did leave out the End If in my post, however my code in my
workbook does have it. Also as I stated before, this is just a very
small version of what I really have so as for the rOpt1Choices I meant
to have that say Range("C1") which I also have in my workbook.

I put the above code in a new workbook with the correct changes and I
still get the error. I think it has something to do with the command
button because it only gives me a problem when I start the macro with
the command button. As for the check of which worksheet I am on , I
can give it a try but am not very hopeful.

Thanks for the input. And if there is anyone else that would like to
give a stab at it I would greatly appreciate it.

Greg Wilson came up with the solution.
Set the command button's TakeFocusOnClick parameter to False.

It was that simple and I spent hours trying to figure it out. Thanks
Greg

Thanks everyone for your responses. I probable never would have got it
on my own.