Help, in a Protection Quandary

    Hi All,


    I am in a quandary over how to implement cell protection. It is common knowledge that for protection to work the cells must be locked and the worksheet protected. Well, when I try to implement cell protection using the following code:


    Sub Worksheet_Activate
    ActiveSheet.Protect UserInterfaceOnly:=True, password:="password"
    End Sub


    Either the target cells remain locked and a "Run-time error 1004' occurs and the macro stops, or the macro executes and leaves the target cells unlocked (and I'd like them to be locked when the macro has executed).


    Can anyone shed light on this quandary? How can I acheive my objective of protecting the cell contents? It has been suggested that I set the sheets scroll area, however, this solution does not work for my situation.


    Also, is there a way to lock cell after it has been unprotected?


    Any help that I could get on this will be greatly appreciated!


    Thanks,
    Chris

    Your code works for me. Maybe you don't have the focus on the activesheet when you start your macro. Try adding something like cells(1,1).select on the first line of your macro.

    code works for me as well when selecting the sheet. However, you mentioned target cells along with runnng a macro, is the macro doing anything to the cells such as formatting? If so you would have to probably do a ScreenUpdating = False or minimizing the window through code upon selection of the sheet, run your macro and insert your protection and ScreenUpdating = True at the end of the macro. If you could give a little psuedocode, we could work out a solution.

    Chris,


    Just a quick though, have you tried stepping through your code (using F8 key) one line at a time to see where it bugs out?


    When I've had similar problems and stepped through the code the problem jumped up and smacked me right between the eyes.


    I know its simple but sometimes we over look the simple in the crisis.


    AJW

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login