The object invoked has disconnected from it's clients modify protected sht validation

[Asterix]

Workbook is protected, sheet is protected userinterfaceonly, have checked and the correct range is selected.
Following code fails on the .Add method with an automation error - 2147417848 (80010108) - The object invoked has disconnected from it's clients. If the sheet is fully unprotected, there is no error - problem is, I need the sheet and the workbook structure protected - ...
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Choices
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Replacing the .Delete and .Add with a single .Modify results in another error...1004 Application-defined or object-defined error.
I'd like the current code to apply the validation shown on the protected workbook/ sheet...can you help?
Oh, I have searched (here, mrexcel, cpearson, microsoft support forums, google, expertexchange) and I can find the error but not as a result of my particular circumstances.
Thanks

[Bob Phillips]

Just unprotet the sheet, apply your code, then reset the protection.

Standard technique, I do it all the time.

[Asterix]

Just unprotet the sheet, apply your code, then reset the protection.

Standard technique, I do it all the time.

Thanks, that works.

So, it's a feature then...just seems a bit inelegant to have to do that (am I just scratching the surface of VBA inelegancies?).

[Bob Phillips]

Well, there are certainly a lot of inelegances, and there are even more holes that should of been plugged but never will be now, but is this an inelegance? The sheet is protected, which means that changes are controlled. If you were in Excel, you would have to unprotect it to make changes to the protected area, why do you expect anything different within VBA?

[Asterix]

why do you expect anything different within VBA?

...because the userinterface statement implies this and it tends towards excellence as opposed to just being ok

[Bob Phillips]

But you locked the userinterface down!

[Dr.K]

I'm with Asterix on this one: I was ****ed off when I figured out that the only reliable way to do it is to Unprotect, do your changes, and then Protect, every single time...

I now understand why it works the way it does, but based on the way the help file reads, it seems like userinterfaceonly SHOULD work they way Asterix and I expected it to. But it doesn't.


EDIT:
Wow, synonyms for "urinate" get replaced by the filter?
In many english-speaking countries, isn't that a euphemism for being intoxicated?

[Bob Phillips]

It certainly is, as a newt.

[Asterix]

But you locked the userinterface down!

"When my washing machine locks the door just after the program starts, I don't expect the machine to stop working."

I'm going to have to get that googlated into French, it would make it sound even more pompous than it isn't supposed to be. Got to go now and take a ****, ta.

[Bob Phillips]

Silly analogies don't hold. Just as you don't expect your washing machine to not work, you don't Excel to stop working, and guess what, it doesn't. It just doesn't do what you want it to do (at that moment, if it did I bet you will find another point qwhen it isn't working). But you don't expect to be able to open the door whilst it is filled with water, whereas normally you do. Same here with Excel, one aspect is not allowed because YOU DECIDED YOU DIDN'T WANT IT THAT WAY.

[Asterix]

Silly analogies don't hold. Just as you don't expect your washing machine to not work, you don't Excel to stop working, and guess what, it doesn't. It just doesn't do what you want it to do (at that moment, if it did I bet you will find another point qwhen it isn't working). But you don't expect to be able to open the door whilst it is filled with water, whereas normally you do. Same here with Excel, one aspect is not allowed because YOU DECIDED YOU DIDN'T WANT IT THAT WAY.

You mean, Microshaft didn't design solely for me? , must raise that oversight with Bill qwhen we're on the next pub crawl.

No need to justify silly* analogies and definitely no need to shout - I can hear you fine from here.

Expecting a kitchen-foil incarcerated Malaysian hogwart to prove Soparti's theory once dropped out of a plane...now that's a silly analogy.

[thinks]Somehow I think whatever I say, you'll have the last word.[/thinks]

[rory]

What exact code did you use in the Workbook_Open event (I assume you used that event) to protect the sheet?

[Asterix]

The workbook is protected and I open it with...

Workbooks.Open .FoundFiles(i)

...where Foundfiles(i) is the workbook name. Unless I'm misinterpreting what you say, I'm not protecting it via the .open method. I'm using the information inside it (but not writing back to it - hopefully) and then using the following line to close it...

Workbooks(filenamestring).Close savechanges:=False

...where filenamestring is the workbook name (I suppose I could've used "Workbooks.Close .Foundfiles(i)" thinking about it).

It's not desirable to make any changes to the workbook I open, hence the false savechanges.

[rory]

I'm talking about the workbook in which you are trying to change the Validation settings. I assume you are using an event to protect the sheets and set the UserInterfaceOnly argument? I think you still have to go the route of Unprotecting and then reprotecting to change Validation, as there seems to be at the very least some inconsistency as to whether it works or not with UserInterfaceOnly, but I just wanted to check your code was correct

[Asterix]

d'oh...see what you mean, that was me being thick. The code in full reads...

ActiveSheet.Unprotect (mypassword)

With range("resource_names").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

ActiveSheet.Protect Password:=mypassword, UserInterfaceOnly:=True

[rory]

Yup, I think that's the best you can do. As far as I can tell, the UserInterfaceOnly option only applies to the options that you can actually specify when you protect a worksheet, and Data Validation is not one of those. It's not a true option to let your code do anything it likes.

[Asterix]

cheers...and given we're less than a week away, merry christmas

[rory]

Likewise. I must get on with some shopping. Actually, no, I'll wait until the 24th as usual.