Link to home
Start Free TrialLog in
Avatar of Kevin
KevinFlag for United States of America

asked on

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
Avatar of hello_everybody
hello_everybody
Flag of South Africa image

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
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kevin

ASKER

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.