Kevin
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
Ratcheting-List--Post-Copy-.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
You must determine if the cells were changed, if they were then Activesheet.Protect.
See http://www.ozgrid.com/VBA/run-macros-change.htm