Avatar of Kevin
Kevin
Flag 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
Microsoft ExcelSpreadsheets

Avatar of undefined
Last Comment
Kevin

8/22/2022 - Mon
hello_everybody

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
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck