VBA - deleting rows in protected sheet

[JamesonMH]

I have a simple tracking worksheet for project costs. The data is in cells (A1:I23). I coded it so the date and time update off to the side at the bottom of the data range (J23:K24 currently). It's also set to lock those date/time cells so users can't mess with it. Everything works except when rows are inserted at bottom of range (and then those rows are late deleted) you get the generic locked sheet message, which I don't want since user s/b able to delete/insert rows at will. I already made it to AllowDeletingRows:=True, so not sure what I'm missing?

Sub [COLOR=#0000ff]Update_Date_Time2[/COLOR]()

Dim ws As Worksheet, D, T As Range
Set ws = ThisWorkbook.Worksheets("Project Tracking")
Set D = Range("J1").Offset(Application.Match("Current as of:", Range("J:J"), 0), 0)
Set T = Range("J1").Offset(Application.Match("Current as of:", Range("J:J"), 0), 1)


If ws.ProtectContents = True Then
    ws.Unprotect Password:="abc"
End If
    
'Set date and time. (The rows move regularly when new costs are added/deleted, so using this approach.)
D = Date
T = "@ " & Time


'Lock the wksht. Protect only the below range. DrawingObjects:=False allows the inserting/editing of objects (like a PDF file).
If ws.ProtectContents = False Then
    'ws.Range(Cells.Address).Locked = False
    ws.Range("$J23:$K24").Locked = True
    ws.Protect Password:="abc", DrawingObjects:=False, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True
End If


End Sub

I also have my worksheet change code below so that the date/time update whenever there's a change to a cell in the relevant range:

Private Sub Worksheet_Change(ByVal Target As Range)'Change Event:
'Check if the target cell (A1:i500) has been changed. 
    If Not Intersect(Target, Range("A1:i500")) Is Nothing Then
        Call [COLOR=#0000ff]Update_Date_Time2[/COLOR]
    End If
End Sub

Thanks!
James

 

[DanteAmor]

You have commented on this line, you must enable it

ws.Range(Cells.Address).Locked = False

Or use:

ws.Cells.Locked = False

 

[JamesonMH]

You have commented on this line, you must enable it

ws.Range(Cells.Address).Locked = False

Or use:

ws.Cells.Locked = False

Thanks Dante, yea I removed it earlier as I didn't understand exactly what it was doing. Can you please explain what that range(cells.address) does?

I just enabled it but it still doesn't work. If I insert rows right above where my time/date is, then delete those same rows afterwards, it says "You are trying to delete a row that contains a locked cell..."

 

[DanteAmor]

Thanks Dante, yea I removed it earlier as I didn't understand exactly what it was doing. Can you please explain what that range(cells.address) does?

ws.Range(Cells.Address).Locked = False

This instruction is to remove the lock in all cells of the sheet.


Then with this instruction ws.Range ("$J23:$K24"). Locked = True only your range will be locked.

I just enabled it but it still doesn't work. If I insert rows right above where my time/date is, then delete those same rows afterwards, it says "You are trying to delete a row that contains a locked cell..."

I do not understand how still error.
I have no problems, I would have to check your file.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[JamesonMH]

Thanks Dante for the link. I'll get setup on Dropbox and put link on this thread tomorrow morning EST.

 

[JamesonMH]

I do not understand how still error.
I have no problems, I would have to check your file.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Hi Dante,

Paste link below to your browser. (I used Microsoft OneDrive instead of DropBox)

https://jhuntleyptbo-my.sharepoint....SY_4o4Ys-VGmwswLk0yviwBmXwUfqSjJhfpfaqfjt5dLQ

When file shows in your browser, Click File - Save As so you can open in regular Excel (not online) and enable macro. Just go to row 22 and insert 3-5 rows. then try to delete those new rows and you'll get the protected wksht message I got.

Thanks for your help.

James

 

[DanteAmor]

I think I know what's up.

Start:
- "Current as of" is in cell J23.

-You go to line 22, insert 3 lines.
- Now the text "Current as of" is in cell J26.

This happens:
- You want to delete the 3 inserted rows, that is, you want to delete rows 23,24 and 25.
But you can't eliminate 23 or 24, because the macro protected cells J23:K24. (remember)
- You can delete row 25,26 and 27. This would delete your text "Current as of"


It occurs to me to make the blocking of cells dynamic with the text "Current as of:"


Try this:

Sub Update_Date_Time2()
  Dim ws As Worksheet, D, T As Range
  Set ws = ThisWorkbook.Worksheets("Tracking")
  Set D = Range("J1").Offset(Application.Match("Current as of:", Range("J:J"), 0), 0)
  Set T = Range("J1").Offset(Application.Match("Current as of:", Range("J:J"), 0), 1)
  
  If ws.ProtectContents = True Then
      ws.Unprotect
  End If
      
  'Set date and time
  D = Date
  T = "@ " & Time
  
  'Lock the wksht. Protect only the below range. DrawingObjects:=False allows the inserting/editing of objects (like a PDF file).
  If ws.ProtectContents = False Then
    Dim f As Range
    ws.Range(Cells.Address).Locked = False
[COLOR=#0000ff]    Set f = ws.Range("J:K").Find("Current as of:", , xlValues, xlWhole)[/COLOR]
[COLOR=#0000ff]    If Not f Is Nothing Then[/COLOR]
[COLOR=#0000ff]      ws.Range("J" & f.Row & ":K" & f.Row + 1).Locked = True[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
    ws.Protect DrawingObjects:=False, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True
  End If
End Sub

 

[JamesonMH]

I think I know what's up.

Start:
- "Current as of" is in cell J23.

-You go to line 22, insert 3 lines.
- Now the text "Current as of" is in cell J26.

This happens:
- You want to delete the 3 inserted rows, that is, you want to delete rows 23,24 and 25.
But you can't eliminate 23 or 24, because the macro protected cells J23:K24. (remember)
- You can delete row 25,26 and 27. This would delete your text "Current as of"


It occurs to me to make the blocking of cells dynamic with the text "Current as of:"


Try this:

Sub Update_Date_Time2()
  Dim ws As Worksheet, D, T As Range
  Set ws = ThisWorkbook.Worksheets("Tracking")
  Set D = Range("J1").Offset(Application.Match("Current as of:", Range("J:J"), 0), 0)
  Set T = Range("J1").Offset(Application.Match("Current as of:", Range("J:J"), 0), 1)
  
  If ws.ProtectContents = True Then
      ws.Unprotect
  End If
      
  'Set date and time
  D = Date
  T = "@ " & Time
  
  'Lock the wksht. Protect only the below range. DrawingObjects:=False allows the inserting/editing of objects (like a PDF file).
  If ws.ProtectContents = False Then
    Dim f As Range
    ws.Range(Cells.Address).Locked = False
[COLOR=#0000ff]    Set f = ws.Range("J:K").Find("Current as of:", , xlValues, xlWhole)[/COLOR]
[COLOR=#0000ff]    If Not f Is Nothing Then[/COLOR]
[COLOR=#0000ff]      ws.Range("J" & f.Row & ":K" & f.Row + 1).Locked = True[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
    ws.Protect DrawingObjects:=False, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True
  End If
End Sub

Now it works - thanks Dante

I never did this type of code before so didn't realize it would be absolute the way I did it instead of relative. Thanks so much for your time, much appreciated!

James

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.