Run a sort macro in a protected WORKBOOK

[Guest]

I have seen may threads on how to create a sort macro for a protected
WORKSHEET... yet I need help for a Shared protected WORKBOOK.

I have a protected worksheet where I created a macro for sorting. My macros
includes the code for unprotecting my worksheet, sorting it and then
re-protecting the worksheet. I thought I was done with my project.... BUT
THEN..

I clicked the Protect and Share workbook option so other can view the data
and sort the data.... and now my sort macro does not work. I'm getting an
error due to the WORKBOOK protection.

Any ideas?????

 

[Dave Peterson]

What version of excel are you using?

xl2002+ has an option to allow the user to sort on a protected worksheet.

If you unlock the cells in those rows and protect the worksheet (allowing the
user to sort that worksheet), then share the workbook, you should still be able
to sort the data.

If you're using xl2k or below, then you have a problem. xl2k and below doesn't
allow all those granular levels of protection.

One way around it is to provide a macro that unprotects the worksheet, does the
sort and reprotects the worksheet. (or use userinterfaceonly option when you
protect the worksheet in code).

The bad news is that you can't change the worksheet protection in a shared
workbook (protection of the workbook isn't the problem--it's the sharedness and
worksheet protection that's the problem).

The only way I know around this in xl2k and below is to make a decision--either
drop the worksheet protection--or don't share the workbook.

 

[Guest]

You know... everything I've researched in Excel says that... but I've tried
it... I'm on Excel 2003 version. My cells that contain data are unlocked, I
protected the worksheet and it no longer let me just sort from the Data
option, it tells my some cells are locked. With the Macro I'm able to bypass
the protection, sort and re-establish my protection. But not as a Shared
workbook.

You know... Is it possible that it is referring to the remaining (empty)
cells that are locked. I locked all other cells that are not being used, so
that when entering data the cursor only goes through the unlocked range.

I can't believe this would be the problem.... but as far as what you had
suggested... I've tried....

I'm going back to the frawing board...
--
Thank you for your time.
"Happiness is not having what you want, but wanting what you have"

What version of excel are you using?

xl2002+ has an option to allow the user to sort on a protected worksheet.

If you unlock the cells in those rows and protect the worksheet (allowing the
user to sort that worksheet), then share the workbook, you should still be able
to sort the data.

If you're using xl2k or below, then you have a problem. xl2k and below doesn't
allow all those granular levels of protection.

One way around it is to provide a macro that unprotects the worksheet, does the
sort and reprotects the worksheet. (or use userinterfaceonly option when you
protect the worksheet in code).

The bad news is that you can't change the worksheet protection in a shared
workbook (protection of the workbook isn't the problem--it's the sharedness and
worksheet protection that's the problem).

The only way I know around this in xl2k and below is to make a decision--either
drop the worksheet protection--or don't share the workbook.

 

[Dave Peterson]

All the cells that are in the range to be sorted have to be unlocked.

And even worse, the cells that are contiguous to that range need to be
unlocked. Maybe you could make sure the range has an empty row before and after
the range--and an empty column to the right/left of the range.

And as long as the workbook is shared, you'll never be able to change protection
of that worksheet. So that's gonna be a dead end.

You know... everything I've researched in Excel says that... but I've tried
it... I'm on Excel 2003 version. My cells that contain data are unlocked, I
protected the worksheet and it no longer let me just sort from the Data
option, it tells my some cells are locked. With the Macro I'm able to bypass
the protection, sort and re-establish my protection. But not as a Shared
workbook.

You know... Is it possible that it is referring to the remaining (empty)
cells that are locked. I locked all other cells that are not being used, so
that when entering data the cursor only goes through the unlocked range.

I can't believe this would be the problem.... but as far as what you had
suggested... I've tried....

I'm going back to the frawing board...

 

[Guest]

Thats exactly what I was afraid of... Back to the drawing board for me. I'll
post my final outcome incase this will help anyone else. Thank you for your
time.
--
Thank you for your time.
"Happiness is not having what you want, but wanting what you have"

All the cells that are in the range to be sorted have to be unlocked.

And even worse, the cells that are contiguous to that range need to be
unlocked. Maybe you could make sure the range has an empty row before and after
the range--and an empty column to the right/left of the range.

And as long as the workbook is shared, you'll never be able to change protection
of that worksheet. So that's gonna be a dead end.