Unprotecting a cell

I Need some help understanding where to place the code to Protect and unprotect my worksheet. Each work book contains 5 buttoms and when one is clicked it unprotects the sheet before running the code, then re-protects the sheet. I had a circular reference error because of the way the formula was written. The problem I am encountering is when the cell that the new code references is selected I get an error saying "The cell or chart you are thrying to change is protected and therefore read-only" I can maually unprotect, then after the change is made the cell protects itself. I would like it to unprotect with out having to click on the unprotect button

Private Sub cmdRndOne_Click()
' Round One Macro (Main Ratchet Round - All except PreCall)
' Macro recorded 5/16/2008 by Mark Baird
'
' Keyboard Shortcut: Ctrl+r
'
    ActiveSheet.Unprotect
    Selection.AutoFilter Field:=5
    Selection.AutoFilter Field:=7
    Range("A4:CZ92").Sort Key1:=Range("A4"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Selection.AutoFilter Field:=5, Criteria1:="="
    Range("A4:CZ92").Sort Key1:=Range("A4"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("U4").Select
    ActiveSheet.Protect
End Sub

Private Sub worksheet_change(ByVal Target As Range)
Dim mytime As String
If ActiveCell.Column = 5 And ActiveCell.Row > 3 Then
    Application.EnableEvents = False
    mytime = Now()
    Range("F" & ActiveCell.Row).Value = Now()
    Range("F" & ActiveCell.Row).Select
    Application.EnableEvents = True
End If
End Sub

Open in new window

Ratcheting-List--Post-Copy-.xls

poucedeleonAsked: 2011-10-01

Who is Participating?

Solutions

LVL 42

dlmilleConnect With a Mentor Commented: 2011-10-02

Your "new code" written in the worksheet_change event (if I decipher your comments, code, and worksheet properly - you may not have left enough hints, but believe I got it) will not work properly. The worksheet protection scheme overrides any attempt to make a worksheet_change, so the worksheet_change event would never fire, unless the sheet were manually unprotected.

I can see from your uploaded sheet (thanks for making it easier to diagnose by uploading the workbook!) and your worksheet_change coding attempt, is that you'd like the user to make changes to Column E, using the Drop-Down list. As a result, you want the timestamp to populate automatically. Hopefully, this is the code/problem you were identifying!

First, you need to unprotect the sheet, then unlock all the possible Column E entries (e.g., from E3 down, select the cells, then format->protection-> uncheck Locked). This will allow the user to make changes using the dropdown list, without getting errors. No need to turn protection off then back on...

Your code also should unprotect then re-protect inside the if statement. Otherwise, it may not reprotect when appropriate.

Finally, you may want to consider adding logic such that if the user deletes the entry in Column E, the date/time stamp would also be erased. I've added that code and commented, accordingly, that you can take it out if that change is unwanted.

As a result, here's your revised code to the worksheet_change() code:

Private Sub worksheet_change(ByVal Target As Range)
Dim mytime As String


    If ActiveCell.Column = 5 And ActiveCell.Row > 3 Then
        ActiveSheet.Unprotect
        Application.EnableEvents = False
        If ActiveCell.Value = "" Then 'comment this part of the if then else out, if clearing the timestamp is not wanted when the entry is deleted
            Range("F" & ActiveCell.Row).Value = ""
        Else
            mytime = Now()
            Range("F" & ActiveCell.Row).Value = Now()
            Range("F" & ActiveCell.Row).Select
        End If
        Application.EnableEvents = True
        ActiveSheet.Protect
    End If

End Sub

Open in new window

Also, the attached workbook has set Column E possible entries (the dropdowns) to unlocked as you don't want these fields protected.
------------------------------------------------------------------------------------------------------------------
One last thing (perhaps an item for a new question): The user may choose to copy/paste several items in Column E. You are using ActiveCell in your logic and as a result, it will only work on one item (not all the item that the user may treat with). If the user were to delete several items (by selecting several and hitting the "delete" key to clear) and with the revised code, again, only the activecell would be affected.

I suggest you loop through the Target range, treating each item in that range with the same logic...

e.g.,

activesheet.unprotect
for each rng in Target
'test each rng - if its blank, then blank the timestamp (your option, as this part of the logic was my add); otherwise, put the time stamp in each
next rng
activesheet.protect
---------------------------------------------------------------------------

See attached worksheet with my modifications in the sheet (unlocking E3:E-lastrow) and code modifications to worksheet_change.

Enjoy!

Dave
Ratcheting-List--Post-Copy-r1.xls

LVL 8

hello_everybodyCommented: 2011-10-01

Remove Activesheet.Protect.
You must determine if the cells were changed, if they were then Activesheet.Protect.

See http://www.ozgrid.com/VBA/run-macros-change.htm

poucedeleonAuthor Commented: 2011-10-02

dlmille, answer was far more helpful than I expected. I have not done much work in excel, but was able to understand the improvement he suggested because the explanation was very clear and detailed. Thank you very much.