Validation code not functioning correctly

[Simon Plenderleith]

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

 

[Bob Phillips]

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

 

[Simon Plenderleith]

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

Thanks,

Simon

 

[Bob Phillips]

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?

 

[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 Autpen 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

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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$100)-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