Partial Editing on A Protected Sheet

[aalansari]

Hello,

I have a protected sheet with a bunch of data. I want to allow users to be able to change values in a single cell only while still retaining most of the protected sheet rules such as not being able to format or delete the cell, adding/editing comments, etc.

My idea is that once the user makes a change, a comment is added to the cell that says that it was edited on this day and time. The same message gets appended to the comment in case the user makes further changes to the cell. To be able to do that, I first checked to see that the selection range was a single cell. Next I unprotected the worksheet which would allow the user to make a change to the cell value. Once a change is detected, the appropriate comment is inserted. However, since I'm unprotecting the sheet, obviously users can do a lot more than just change the values and that's which is why I was protecting the sheet in the first place.

Any ideas on how I would be able to do that?

Thanks

 

[Gerald Higgins]

Have you considered changing the status of the single cell by unticking the LOCKED option in the Format Cells dialog box, Protection tab ?

That way you could still leave worksheet protection on.

 

[aalansari]

Thanks Gerald!

What I ended up doing is a combination of a Userinterfaceonly protection along with disabling the locked cell. That way I can edit the contents manually and add comments via VBA

 

[airamer]

I am trying to do the same thing with one of my files. Can you please describe how you achieved your end result? Any examples of code/templates would be greatly appreciated!

 

[tmd63]

Go to the cell you want un-protecting. Highlight it. Right click and select 'Format Cells'. Click the protection tab and untick the Locked option.
When the page is protected, this cell will remain unprotected and you can enter data into it.

 

[aalansari]

I am trying to do the same thing with one of my files. Can you please describe how you achieved your end result? Any examples of code/templates would be greatly appreciated!

---

In order to do what I wanted via VBA, I had to do the following:

First, I protected the sheet I wanted with "UserInterfaceOnly" enabled. If you simply protect the sheet, then unlocking the cell wouldn't work unless that option is enabled. I also disabled the "move after return" (selection moving down after return key is pressed). This is because when you delete the values in a cell, the selection doesn't move but if you edit and press return it does; therefore, to make the code less complicated I simply disabled it whenever the workbook opens and enabled it when the workbook closes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.MoveAfterReturn = True
End Sub


Private Sub Workbook_Open()
    protector
    'prevents selection of moving down when return key is pressed
    Application.MoveAfterReturn = False
End Sub

Next, I needed a way to ensure that the only a single cell at a time can be unlocked:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


r = Selection.Row
c = Selection.Column


'Unlocks the cell if only a single cell is selected
If Selection.Rows.Count = 1 And Selection.Columns.Count = 1 Then
    Sheet1.Cells(r, c).Locked = False
End If




End Sub

Finally, if a change is made in the selected cell, then the following code is executed:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range


r = Selection.Row
c = Selection.Column


'Target Cell
Set rng = Sheet1.Cells(r, c)


'Checks if cell is blank and has no comment
If rng.Value = "" And rng.Comment Is Nothing Then
    Sheet1.Unprotect Password:="pass"
    rng.AddComment "Cell value was deleted on " & Now & "."
'Checks if cell is blank but has comment
ElseIf rng.Value = "" Then
    Sheet1.Unprotect Password:="pass"
    rng.Comment.Text "Cell value was deleted on " & Now & "." & vbLf, , False


Else
    'Adds comment if cell isn't blank, but contains a comment
       If rng.Value <> "" And rng.Comment Is Nothing Then
                Sheet1.Unprotect Password:="pass"
                rng.AddComment "Cell value was edited on " & Now & "."
            Else
            'Appends comment
                    Sheet1.Unprotect Password:="pass"
                    rng.Comment.Text "Cell value was edited on " & Now & ". " & vbLf, , False
                    rng.Comment.Shape.TextFrame.AutoSize = True
            'Auto-sizing comment
                    If rng.Comment.Shape.Width > 300 Then
                        lArea = rng.Comment.Shape.Width * rng.Comment.Shape.Height
                         rng.Comment.Shape.Width = 200
                        ' An adjustment factor of 1.1
                         ' seems to work ok.
                         rng.Comment.Shape.Height = (lArea / 200) * 1.1
                    End If
        End If
    
End If


'Protects the sheet
Sheet1.protect Password:="pass", userinterfaceonly:=True





End Sub

In my case...

I have a table that gets data from a userform. The sheet is protected to prevent users from formatting the table or making any changes to the sheet. However, I wanted to allow users to be able to edit the content of the cells without unprotecting the whole sheet. So with the above code, any time the user makes a change in the cell, a comment is inserted with a note that the contents were edited along with the date/time in which it was made.

Side note
If you can tell, the comments are being added to the selected cell. So if I edit a cell and to confirm press any other key like down, left,tab, etc. instead of the return key, the comment would be added there. So I still need to figure out how to add the comments to the original selection.

I hope this helps

 

[aalansari]

After some digging and help from others I learned that the code will work exactly how I needed it to if I simple changed the

set rng = sheet1.cells(r,c)

to

set rng = target

.