I get the error "Run-time error '1004': Application-defined or
object-defined error" on the following piece of code:

Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add xlValidateList, , "=$M$1:$M$" & Cnt_Stock
End With
Next x

(The variable Cnt_Stock is already predefined with an integer such as 25)

The .Validation.Add line is pointed to when debugging the error. I can't
see what is causing this. I have the worksheet protected using VBA, like
so:

Sheets("Invoice").Protect Password:="sms", UserInterfaceOnly:=True

And this seems to work fine on other worksheets so that the VBA can
manipulate the worksheet without having to change the protection, and the
user interface protection remains.

Any help is really appreciated!

Thanks,

Simon Plenderleith

Simon,

You are trying to put a formula in the Operator argument position. Either
name the arguments like
Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add Type:=xlValidateList, _
Formula1:="=$M$1:$M$" & Cnt_Stock
End With
Next x


or add an extra comma like

.Validation.Add Type:=xlValidateList, , , "=$M$1:$M$" &
Cnt_Stock

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
I get the error "Run-time error '1004': Application-defined or
object-defined error" on the following piece of code:

Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add xlValidateList, , "=$M$1:$M$" & Cnt_Stock
End With
Next x

(The variable Cnt_Stock is already predefined with an integer such as 25)

The .Validation.Add line is pointed to when debugging the error. I can't
see what is causing this. I have the worksheet protected using VBA, like
so:

Sheets("Invoice").Protect Password:="sms", UserInterfaceOnly:=True

And this seems to work fine on other worksheets so that the VBA can
manipulate the worksheet without having to change the protection, and the
user interface protection remains.

Any help is really appreciated!

Thanks,

Simon Plenderleith

Ah yes, that was a silly error, but neither of those work either :-S Any
more suggestions please... :-|

Thanks,

Simon

"Bob Phillips" wrote in message
...
Simon,

You are trying to put a formula in the Operator argument position. Either
name the arguments like
Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add Type:=xlValidateList, _
Formula1:="=$M$1:$M$" & Cnt_Stock
End With
Next x


or add an extra comma like

.Validation.Add Type:=xlValidateList, , , "=$M$1:$M$" &
Cnt_Stock

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
I get the error "Run-time error '1004': Application-defined or
object-defined error" on the following piece of code:

Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add xlValidateList, , "=$M$1:$M$" & Cnt_Stock
End With
Next x

(The variable Cnt_Stock is already predefined with an integer such as
25)

The .Validation.Add line is pointed to when debugging the error. I
can't
see what is causing this. I have the worksheet protected using VBA,
like
so:

Sheets("Invoice").Protect Password:="sms", UserInterfaceOnly:=True

And this seems to work fine on other worksheets so that the VBA can
manipulate the worksheet without having to change the protection, and
the
user interface protection remains.

Any help is really appreciated!

Thanks,

Simon Plenderleith

Simon,

I tried it, and putting 1..25 in M1:M25, I get a dropdown list in C10:C29
with those values selectable. It even worked if I had a sheet other than
Invoice active.

So what exactly do you mean when you say it still doesn't work? What happens
for you?

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
Ah yes, that was a silly error, but neither of those work either :-S Any
more suggestions please... :-|

Thanks,

Simon

"Bob Phillips" wrote in message
...
Simon,

You are trying to put a formula in the Operator argument position.
Either
name the arguments like
Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add Type:=xlValidateList, _
Formula1:="=$M$1:$M$" & Cnt_Stock
End With
Next x


or add an extra comma like

.Validation.Add Type:=xlValidateList, , , "=$M$1:$M$" &
Cnt_Stock

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
I get the error "Run-time error '1004': Application-defined or
object-defined error" on the following piece of code:

Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add xlValidateList, , "=$M$1:$M$" & Cnt_Stock
End With
Next x

(The variable Cnt_Stock is already predefined with an integer such as
25)

The .Validation.Add line is pointed to when debugging the error. I
can't
see what is causing this. I have the worksheet protected using VBA,
like
so:

Sheets("Invoice").Protect Password:="sms", UserInterfaceOnly:=True

And this seems to work fine on other worksheets so that the VBA can
manipulate the worksheet without having to change the protection, and
the
user interface protection remains.

Any help is really appreciated!

Thanks,

Simon Plenderleith

Hmm... I still get the error ""Run-time error '1004': Application-defined or
object-defined error".

Would you be able to look at my spreadsheet as it's a little complex to
explain... If so I would be VERY VERY grateful :)

Just go to http://simon.dionsys.com/files/SOS_SMS.zip and the Excel
spreadsheet is in there. When you're on the main menu worksheet click the
'Generate Invoice' button and you should get the error I'm getting.

- The Auto_Open macro protects all the sheets appropriately.
- The Fill_Stock_List macro sets up the validation on the cells (the macro
which has the problem code).

I think that's all... please ask if you need any extra info. Your help so
far is very much appreciated as this is an important spreadsheet project
that I have to get completed.

Thanks,

Simon Plenderleith

---------------------------

"Bob Phillips" wrote in message
...
Simon,

I tried it, and putting 1..25 in M1:M25, I get a dropdown list in C10:C29
with those values selectable. It even worked if I had a sheet other than
Invoice active.

So what exactly do you mean when you say it still doesn't work? What
happens
for you?

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
Ah yes, that was a silly error, but neither of those work either :-S
Any
more suggestions please... :-|

Thanks,

Simon

"Bob Phillips" wrote in message
...
Simon,

You are trying to put a formula in the Operator argument position.
Either
name the arguments like
Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add Type:=xlValidateList, _
Formula1:="=$M$1:$M$" & Cnt_Stock
End With
Next x


or add an extra comma like

.Validation.Add Type:=xlValidateList, , , "=$M$1:$M$" &
Cnt_Stock

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
I get the error "Run-time error '1004': Application-defined or
object-defined error" on the following piece of code:

Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add xlValidateList, , "=$M$1:$M$" &
Cnt_Stock
End With
Next x

(The variable Cnt_Stock is already predefined with an integer such
as
25)

The .Validation.Add line is pointed to when debugging the error. I
can't
see what is causing this. I have the worksheet protected using VBA,
like
so:

Sheets("Invoice").Protect Password:="sms",
UserInterfaceOnly:=True

And this seems to work fine on other worksheets so that the VBA can
manipulate the worksheet without having to change the protection,
and
the
user interface protection remains.

Any help is really appreciated!

Thanks,

Simon Plenderleith

When I broke the password on your invoice sheet and unprotected it, your
code ran fine. I tried just using UserInterfaceOnly:=true without
unprotecting, but I still got the error, so the sheet needs to be
unprotected. You can unprotect it in your routine just before adding the
data validation, then reprotect it after.


Regards,
Tom Ogilvy


Simon Plenderleith wrote in message
...
Hmm... I still get the error ""Run-time error '1004': Application-defined
or
object-defined error".

Would you be able to look at my spreadsheet as it's a little complex to
explain... If so I would be VERY VERY grateful :)

Just go to http://simon.dionsys.com/files/SOS_SMS.zip and the Excel
spreadsheet is in there. When you're on the main menu worksheet click the
'Generate Invoice' button and you should get the error I'm getting.

- The Auto_Open macro protects all the sheets appropriately.
- The Fill_Stock_List macro sets up the validation on the cells (the macro
which has the problem code).

I think that's all... please ask if you need any extra info. Your help so
far is very much appreciated as this is an important spreadsheet project
that I have to get completed.

Thanks,

Simon Plenderleith

---------------------------

"Bob Phillips" wrote in message
...
Simon,

I tried it, and putting 1..25 in M1:M25, I get a dropdown list in
C10:C29
with those values selectable. It even worked if I had a sheet other than
Invoice active.

So what exactly do you mean when you say it still doesn't work? What
happens
for you?

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
Ah yes, that was a silly error, but neither of those work either :-S
Any
more suggestions please... :-|

Thanks,

Simon

"Bob Phillips" wrote in message
...
Simon,

You are trying to put a formula in the Operator argument position.
Either
name the arguments like
Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add Type:=xlValidateList, _
Formula1:="=$M$1:$M$" & Cnt_Stock
End With
Next x


or add an extra comma like

.Validation.Add Type:=xlValidateList, , , "=$M$1:$M$" &
Cnt_Stock

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
I get the error "Run-time error '1004': Application-defined or
object-defined error" on the following piece of code:

Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add xlValidateList, , "=$M$1:$M$" &
Cnt_Stock
End With
Next x

(The variable Cnt_Stock is already predefined with an integer such
as
25)

The .Validation.Add line is pointed to when debugging the error.
I
can't
see what is causing this. I have the worksheet protected using
VBA,
like
so:

Sheets("Invoice").Protect Password:="sms",
UserInterfaceOnly:=True

And this seems to work fine on other worksheets so that the VBA
can
manipulate the worksheet without having to change the protection,
and
the
user interface protection remains.

Any help is really appreciated!

Thanks,

Simon Plenderleith

An alternative would be to put use a dynamic name as the source of your list
and put in the data validation manually as part of your template.

Insert=Name=Define

Name: IList
Refersto: =Offset(Invoice!$M$1,0,0,CountA(Invoice!$M$1:$M$10 0)-1,1)


Then define this as the source for your list in data validation (add the
data validation manually)

=Ilist

That worked for me.

Regards,
Tom Ogilvy


Simon Plenderleith wrote in message
...
Hmm... I still get the error ""Run-time error '1004': Application-defined
or
object-defined error".

Would you be able to look at my spreadsheet as it's a little complex to
explain... If so I would be VERY VERY grateful :)

Just go to http://simon.dionsys.com/files/SOS_SMS.zip and the Excel
spreadsheet is in there. When you're on the main menu worksheet click the
'Generate Invoice' button and you should get the error I'm getting.

- The Auto_Open macro protects all the sheets appropriately.
- The Fill_Stock_List macro sets up the validation on the cells (the macro
which has the problem code).

I think that's all... please ask if you need any extra info. Your help so
far is very much appreciated as this is an important spreadsheet project
that I have to get completed.

Thanks,

Simon Plenderleith

---------------------------

"Bob Phillips" wrote in message
...
Simon,

I tried it, and putting 1..25 in M1:M25, I get a dropdown list in
C10:C29
with those values selectable. It even worked if I had a sheet other than
Invoice active.

So what exactly do you mean when you say it still doesn't work? What
happens
for you?

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
Ah yes, that was a silly error, but neither of those work either :-S
Any
more suggestions please... :-|

Thanks,

Simon

"Bob Phillips" wrote in message
...
Simon,

You are trying to put a formula in the Operator argument position.
Either
name the arguments like
Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add Type:=xlValidateList, _
Formula1:="=$M$1:$M$" & Cnt_Stock
End With
Next x


or add an extra comma like

.Validation.Add Type:=xlValidateList, , , "=$M$1:$M$" &
Cnt_Stock

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Simon Plenderleith" wrote in message
...
I get the error "Run-time error '1004': Application-defined or
object-defined error" on the following piece of code:

Dim x As Integer

For x = 10 To 29
With Sheets("Invoice").Range("$C$" & x)
.Validation.Delete
.Validation.Add xlValidateList, , "=$M$1:$M$" &
Cnt_Stock
End With
Next x

(The variable Cnt_Stock is already predefined with an integer such
as
25)

The .Validation.Add line is pointed to when debugging the error.
I
can't
see what is causing this. I have the worksheet protected using
VBA,
like
so:

Sheets("Invoice").Protect Password:="sms",
UserInterfaceOnly:=True

And this seems to work fine on other worksheets so that the VBA
can
manipulate the worksheet without having to change the protection,
and
the
user interface protection remains.

Any help is really appreciated!

Thanks,

Simon Plenderleith