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!

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

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