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.ProtectEnd SubPrivate Sub worksheet_change(ByVal Target As Range)Dim mytime As StringIf 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 = TrueEnd IfEnd Sub
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.
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
You must determine if the cells were changed, if they were then Activesheet.Protect.
See http://www.ozgrid.com/VBA/run-macros-change.htm