XL07 Shared Workbook Unprotect Method Failing

Hi,

Like the new look!

I have a problem and decided to come here as a first port of call rather than last because this forum has always pointed me in the right direction.

I have a pretty complex XL07 workbook with many macros which contain functions that require that the target sheets/ranges to which they are applied be unprotected prior to execution (and then re-protected on exit).

It was always my intention to allow this workbook to be shared out, once the major development wound down but I somehow missed the protection limitation on shared workbooks whereby you can protect all of your required elements etc prior to sharing, but once sharing is enabled, the protection method no longer works.

So all of my macros are failing to execute: Unprotect method of Worksheet class failing etc, as a result of this limitation.

As complete removal of protection is not a runnner for obvious reasons, I would really appreciate if anybody could suggest any methodology or programming technique that might be available to overcome this issue.

Yours in hope
Orson.

Dom,

Thanks for the quick response, and the interesting suggestion.

I have a query on the applicability however.

The bottom line is that I have to assume no access to XL's built-in protect method to protect my data, because my functions within the macros will not operate on protected sheets and shared workbooking will not allow temporary removal of such protection to facilitate macro execution.

You have specified a method by which only cells witin a defined range can be selected, presumably this range would be analogous to an unprotected range then.

So how then are the ranges that do require protection afforded this protection in the absence of the XL built-in protect functionality as above?

I am probably missing something fundamental and would be grateful for further clarification.

Rgds
Orson.

Ah got you now Dom, thanks for the clarification, that looks like it could be a runner!

One more thing if you could:

One of my sheets in particular has several hundred columns, which will include many non-contiguous column ranges, some of which need to be protected, some not.

Can the macro be easily amended so as to allow specification of a series of 'unprotected' ranges within the sheet? Or could I go even further and specify a series of ranges within a series of worksheets, all contained within the same macro, or would it be more practical/feasible to have such a macro in place for each individual sheet?

As a throwaway question possibly not answerable without trying it out, would you anticipate much overhead in terms of speed and responsiveness with such macros in place?

Many thanks for help
Orson.

Hi Dom,

Thanks again for response, I have checked it out and it does exactly what it says in the tin. I am not sure whether it will be workable in practice as it does not allow the user to skip over the protected ranges using the arrow keys or carriage return, tab etc. and unobstructed progression from one unprotected field to the next will be an essential component of the data entry process that will be required for the sheets in question.

I reckon that addressing this issue may be quite complicated though? (Sort of like re-inventing the wheel, would like to avoid sharing myself but I have made a commitment to try my best to implement it).

Not to worry if things are getting a little complicated, I really appreciate your help to date, and in the absence of any further progress on this, I will leave the problem open in case anybody else has any other suggestions or workarounds.

Cheers
Orson.