Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format columns & cells in a protected sheet - VBA code
have a workbook with many VBA macros and existing events on each worksheet.
Most of the macros begin with ActiveSheet.Unprotect and end with ActiveSheet.Protect. My users have asked that I add a function to allow them to adjust column width. I tried manually unprotecting a sheet, then using the menu to select Tools-Protection-Protect Sheet - at which point the boxes to allow all users to 'Select Locked Cells' and 'Select Unlocked Cells' are already selected. I added a check to 'Format Columns' then clicked OK. At this point, I could widen columns. However, after running a macro with ActiveSheet.Protect at the end, I was back to my earlier settings. Can someone help me with the code I should add to my macros to allow users to format columns in a protected sheet? I do not want a password to be used for protection. I would also like users to be able to change row height or allow cells to expand if text is too long, but I DO NOT want them to be able to unhide hidden rows. Any help you can give will be appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format columns & cells in a protected sheet - VBA code
On Tue, 10 Feb 2009 13:21:02 -0800, Dana M
wrote: have a workbook with many VBA macros and existing events on each worksheet. Most of the macros begin with ActiveSheet.Unprotect and end with ActiveSheet.Protect. My users have asked that I add a function to allow them to adjust column width. I tried manually unprotecting a sheet, then using the menu to select Tools-Protection-Protect Sheet - at which point the boxes to allow all users to 'Select Locked Cells' and 'Select Unlocked Cells' are already selected. I added a check to 'Format Columns' then clicked OK. At this point, I could widen columns. However, after running a macro with ActiveSheet.Protect at the end, I was back to my earlier settings. Can someone help me with the code I should add to my macros to allow users to format columns in a protected sheet? I do not want a password to be used for protection. I would also like users to be able to change row height or allow cells to expand if text is too long, but I DO NOT want them to be able to unhide hidden rows. Any help you can give will be appreciated! Dana The Protect method has a number of optional arguments. One of them is 'AllowFormattingColumns' which if you set to TRUE should allow what you want. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format columns & cells in a protected sheet - VBA code
Thanks very much. Sorry for the double post - I got a error message saying
my question hadn't been posted. Looks like I did it twice. "Dick Kusleika" wrote: On Tue, 10 Feb 2009 13:21:02 -0800, Dana M wrote: have a workbook with many VBA macros and existing events on each worksheet. Most of the macros begin with ActiveSheet.Unprotect and end with ActiveSheet.Protect. My users have asked that I add a function to allow them to adjust column width. I tried manually unprotecting a sheet, then using the menu to select Tools-Protection-Protect Sheet - at which point the boxes to allow all users to 'Select Locked Cells' and 'Select Unlocked Cells' are already selected. I added a check to 'Format Columns' then clicked OK. At this point, I could widen columns. However, after running a macro with ActiveSheet.Protect at the end, I was back to my earlier settings. Can someone help me with the code I should add to my macros to allow users to format columns in a protected sheet? I do not want a password to be used for protection. I would also like users to be able to change row height or allow cells to expand if text is too long, but I DO NOT want them to be able to unhide hidden rows. Any help you can give will be appreciated! Dana The Protect method has a number of optional arguments. One of them is 'AllowFormattingColumns' which if you set to TRUE should allow what you want. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format columns and cells in a protected sheet | Excel Programming | |||
Edit text format in non-protected cells in protected worksheet | Excel Discussion (Misc queries) | |||
How can I format unlocked cells when the work sheet is protected | Excel Discussion (Misc queries) | |||
TEX TO COLUMNS PROTECTED SHEET | Excel Discussion (Misc queries) | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |