Excel VBA and Sheet Protection Problem

[TheRobster]

Hi all,

I'm just putting the finishing touches to an Excel application and have started to protect the worksheets using the security function so that users can only enter values into certain cells.

However, after doing this and then testing the worksheet I came across a very big problem! I have quite a lot of VBA code in this application that carries out a lot of calculations and then writes these to various worksheets. After I added the worksheet protection I found that whenever I try to run the VBA code I get errors stating in effect that the computer can't write the results of the VBA calculations to the worksheets as they are protected!

This is most annoying since I only want to stop the users from entering data in the wrong cells - I don't want to stop the computer from been able to write data into them!

In the Protect Worksheet options dialog box I've tried checking all the available options but it still can't write anything to the worksheet.

The only solution that I can think of is to go through the code to see which cells it's trying to change and then Unlock them all in the Protection dialog box. However this would be extremely inefficient as the application is large and was developed in an ad-hoc manner and so there are thousands of cells all over the place that need writing to.

Isn't there any way to allow the VBA code to write to a protected worksheet?

Thanks
-Rob

[RobDog888]

Your best bet I think is to unprotect the entire sheet, do your processing and
then protect just those cells again.

[TheRobster]

Your best bet I think is to unprotect the entire sheet, do your processing and
then protect just those cells again.

This is for a commericial application though, so I don't think it wise to expect people to do that every time they want to do a calculation!

This seems to be a bit of a flaw in the protect worksheet function if it means that the VBA code can't alter the worksheets unless you unprotect every cell that it writes to.

If I have to do this by hand then it'll take ages. I have literally thousands of cells that will need unprotecting.

[RobDog888]

But you can Protect/UnProtect the entire sheet(s) through code.

[dglienna]

use a macro!

[RobDog888]

use a macro!

David, in his original post he states that ...

I have quite a lot of VBA code in this application that carries out a lot of calculations and then writes these to various worksheets. After I added the worksheet protection I found that whenever I try to run the VBA code I get errors stating in effect that the computer can't write the results of the VBA calculations to the worksheets as they are protected!

He's already writting code in the VBA IDE and needs a ways to resolve the isue. A macro will not be of any help.

TheRobster, if you look into my previous post about Protection, you should be able to resolve this. At the beginning of a function make a call to remove the protection for the sheet. Then at the end of the function turn protection back on.

expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Locking cells or hiding formulas has no effect unless the worksheet is protected.

HTH