Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run a sort macro in a protected WORKBOOK
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????? -- Thank you for your time. "Happiness is not having what you want, but wanting what you have" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run a sort macro in a protected WORKBOOK
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. Aggies wrote: 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????? -- Thank you for your time. "Happiness is not having what you want, but wanting what you have" -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run a sort macro in a protected WORKBOOK
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" "Dave Peterson" wrote: 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. Aggies wrote: 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????? -- Thank you for your time. "Happiness is not having what you want, but wanting what you have" -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run a sort macro in a protected WORKBOOK
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. Aggies wrote: 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" "Dave Peterson" wrote: 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. Aggies wrote: 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????? -- Thank you for your time. "Happiness is not having what you want, but wanting what you have" -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run a sort macro in a protected WORKBOOK
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" "Dave Peterson" wrote: 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. Aggies wrote: 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" "Dave Peterson" wrote: 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. Aggies wrote: 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????? -- Thank you for your time. "Happiness is not having what you want, but wanting what you have" -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel macro to open a password protected workbook | Excel Discussion (Misc queries) | |||
can i sort a protected sheet | Excel Discussion (Misc queries) | |||
How to Sort Protected Worksheet | Excel Worksheet Functions | |||
Sort on a protected sheet | Excel Worksheet Functions | |||
Multiple workbook user's with Master workbook - all password protected | Excel Discussion (Misc queries) |