VBA with password-protected sheet

[KJP03]

Hi Everyone,

I have a fairly limited experience of VBA but have managed to incorporate the below code to update Colulmns O, P, and Q with the date of last update (Column O), cell updated (Column P), and last updated by (Column Q) for any changes made in the target range R2:AK1000:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("R2:AK1000")) Is Nothing Then Exit Sub
Cells(Target.Row, "O") = Now()
Cells(Target.Row, "P") = Target.Address(RowAbsolute:=False)
Cells(Target.Row, "Q") = Application.UserName
Application.EnableEvents = True
End Sub

What I'm aiming to do is to password protect the entire sheet with Column O, Column P, and Column Q all locked but where the VBA code can still automatically update these columns when changes are made as per the original (above) code. In the target range (R2:AK1000) there is a mixed economy of locked and unlocked columns/cells. I've searched potential solutions on the web but can't seem to find an answer to my specific VBA code.

Excel version: 365 (but running in the application)
Operating system: Windows 10

Any help is appreciated! Many thanks in advance!

 

[Logit]

See the two answers at the bottom :

Unlocking Edit Ranges In Excel Using VBA

 

[KJP03]

See the two answers at the bottom :

Unlocking Edit Ranges In Excel Using VBA

Thanks for the above guidance Logit. I've attempted to incorporate the three techniques on offer but to no avail! Method 2 provided by Rajesh works insofar as protecting the cells for certain users but renders my VBA code useless as it will no longer update columns O, P, and Q when changes are made in the R2:AK1000 range.

Are there any other techniques to resolve my issue that you're aware of?

 

[Logit]

Here is another example :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Dim inputRange As Range


Set ws = Worksheets("Sheet1")
'tell this sub to unprotect only these cells
Set inputRange = Range("B11,C11,C15")


' If the selected cell is not in the range keep the sheet locked
If Intersect(Target, inputRange) Is Nothing Then
'else unprotect the sheet by providing password
'(same as the one that was used to protect this sheet)
Else

    'ws.Unprotect Password:="abc"
    'Target.Locked = False
    'ws.Protect Password:="abc"
    
    Dim PassWord As String, i As Integer
  i = 0
  Do
    i = i + 1
    If i > 3 Then
      MsgBox "Sorry, Only three tries"
        Application.DisplayAlerts = False
        ThisWorkbook.Saved = True
        Application.Visible = False
        Application.Quit
      Exit Sub
    End If
    PassWord = InputBox("Enter Password")
    Loop Until PassWord = "abc"
    If PassWord = "abc" Then
    Dim cel As Range
    ActiveSheet.Unprotect PassWord:="abcd"
    For Each cel In Target
        If cel.Value <> "" Then
            cel.Locked = True
        End If
    Next cel
    End If


End If

End Sub

 

[KJP03]

Thanks Logit, it's much appreciated. I've copied and pasted the above code into the "ThisWorkbook" object and updated "Sheet1" to "Sheet6" to ensure it's targeting the correct sheet in my workbook. In terms of range, I want to keep the R3:AM1000 range unprotected so I set this to ("R3:AM1000"). Everything else I kept the same and I password-protected Sheet6 with "abc". However, when I updated a cell in the unprotected range (e.g., $R$10), I received the following message:

Run-time error '1004':

The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.

When selecting "Debug" it took me through to the VBA code for Sheet6 and highlighted the first row of the below section in yellow:

Cells(Target.Row, "O") = Now()
Cells(Target.Row, "P") = Target.Address(RowAbsolute:=False)
Cells(Target.Row, "Q") = Application.UserName

My understanding is that Excel did this because columns O, P, and Q are protected and O is the first column it encounters in the code. Going back to my original issue here ... the only cells I want to remain unprotected are R3:AM1000 inclusive. Everything else can remain locked but all cells in Column O, P, and Q should be both locked and updated by the VBA code when a change is made in the unprotected range (R3:AM1000). The only exception here would be rows 1 and 2 where each column/cell would remain unlocked/unprotected but this is outside the data/VBA range in any case.

Is this possible? Have in interpreted/applied the above code you provided incorrectly?

 

[Logit]

Following the guideline in Answer #2 on url : Unlocking Edit Ranges In Excel Using VBA, I was able
to complete the goal you have set out (if I correctly understand what it is you are attempting to do).

I place your VBA code in the Sheet1 code window rather (than a Regular Module) so anytime a change is made in the Range R2:AK1000 it runs
the macro.

Download workbook : Internxt Drive – Private & Secure Cloud Storage

For simplicity sake, I reduced the size of the range to just several rows.