[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Unprotecting a cell

Posted on 2011-10-01
3
Medium Priority
?
603 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:poucedeleon
3 Comments
 
LVL 8

Expert Comment

by:hello_everybody
ID: 36898506
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
0
 
LVL 42

Accepted Solution

by:
dlmille earned 1000 total points
ID: 36898820
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
0
 

Author Closing Comment

by:poucedeleon
ID: 36901133
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question