Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro on protected sheet-error
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro on protected sheet-error
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). "michaelberrier" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro on protected sheet-error
Can you unprotect the worksheet
do the sort reprotect the worksheet All in your code? michaelberrier wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro on protected sheet-error
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. Paul Mathews wrote: 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). "michaelberrier" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro on protected sheet-error
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? Dave Peterson wrote: Can you unprotect the worksheet do the sort reprotect the worksheet All in your code? michaelberrier wrote: 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. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro on protected sheet-error
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. "michaelberrier" wrote: 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? Dave Peterson wrote: Can you unprotect the worksheet do the sort reprotect the worksheet All in your code? michaelberrier wrote: 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. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro on protected sheet-error
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding and compiling list of cells containing data... | Excel Worksheet Functions | |||
Macro Errs when sheet is protected | Excel Worksheet Functions | |||
Can a macro format a hidden sheet? | Excel Discussion (Misc queries) | |||
how to hide rows in a protected sheet | Excel Worksheet Functions | |||
Why can't my macro use Auto Filter when I told the Sheet Protecti. | Excel Worksheet Functions |