macro on protected sheet-error

[michaelberrier]

One of many macros in this sheet sorts a range of data based on
specific key cells. The sort will not execute if the worksheet is
protected. It gives the following error:

Runtime Error '1004'
Sort method of range class failed.

Here is the code at the point where it fails:

Range("A7:I999").Select
Selection.Sort Key1:=Range("A8"), Order1:=xlAscending,
Key2:=Range("B8") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom

All the cells in that range are unlocked. I've tried reducing the
range size to only one or two cells to isolate the offender, but the
error persists. Of course, VBE help was none.

Thanks to all.

 

[Guest]

Hi Michael,

One simple approach would be, when protecting the worksheet, check the
"Sort" item in the "Allow users of this worksheet to:" check list. Note that
this selectivity in protecting items in a worksheet doesn't extend prior to
Excel XP (but is available in Excel XP and 2003).

 

[Dave Peterson]

Can you unprotect the worksheet
do the sort
reprotect the worksheet

All in your code?

 

[michaelberrier]

Paul,
Thanks alot. I always thought that as long as cells were unlocked,
then they wouldn't be affected by protecting the rest of the sheet.
Your advice helped me solve another 100 problems I would have had.

Thanks again.

 

[michaelberrier]

Dave & Paul:
Thanks for the help, but now I have a different problem.

I think one of you helped me with calling specific macros from a combo
box, and it worked fine, UNTIL I protected the sheet.

Now, when calling the macro from the combo box, it will usually pop up
an "Exception Occurred" box. If I run the macros manually or even from
a Forms button, they work fine. It is only trying to run from the
Combo box that gives the error. I tried running it without the sheet
protected and it works just fine.

Is there something I need to check or uncheck in the Protect Sheet box
like I had to do with Sort?

 

[Guest]

Hi Michael, I don't think I helped you with calling a macro using a combo box
but I suspect that you're using a combo control from the Forms toolbar
(rather than the Control Toolbox toolbar) to accomplish this. If so, then
the combo has a linked cell somewhere in your workbook that contains the
index value of whatever choice you make in the combo (and the VBA code
associated with the combo executes a particular piece of code depending on
that value probably via a Select Case statement). Assuming this is the case,
you'll need to unprotect the linked cell. You can find the cell by first
unprotecting the worksheet, right-clicking the combo, selecting "Format
control...", then selecting the "Control" tab.

Dave & Paul:
Thanks for the help, but now I have a different problem.

I think one of you helped me with calling specific macros from a combo
box, and it worked fine, UNTIL I protected the sheet.

Now, when calling the macro from the combo box, it will usually pop up
an "Exception Occurred" box. If I run the macros manually or even from
a Forms button, they work fine. It is only trying to run from the
Combo box that gives the error. I tried running it without the sheet
protected and it works just fine.

Is there something I need to check or uncheck in the Protect Sheet box
like I had to do with Sort?

 

[michaelberrier]

Paul,
You were half right, but it solved the whole problem.

I am doing it from a control combo box on a Userform, not from a Forms
combo box. But, I did have the ContorlSource set to a protected cell.
So, I got rid of that and the problem fixed itself.

thanks again.