Protected cells - want to enable comments

[poquist]

Hi all, thank you in advance for any help you can provide. I am running Excel 2016 on Windows 10 Pro.

Description of scenario: I want to allow users to insert a comment within a specified range of cells. All other cells in the worksheet are protected in order to 1) avoid inadvertent alteration of formulas and 2) prevent changing old data. Cell A1 typically has the TODAY formula but for purposes of this post I entered 7/19/19 instead (see attached file)

Private Sub Workbook_Open()


ActiveSheet.Unprotect Password:="VBAexpress"
If Range("A1").Value <= Range("S2").Value + 3 Then
    Range("E4:S11").Locked = False
    Range("B1").Value = 0
ElseIf Range("A1").Value > Range("S2").Value + 3 Then
    Range("E4:S11").Locked = True
    Range("B1").Value = 1
End If


If Range("A1").Value <= Range("T14").Value + 3 Then
    Range("E16:T23").Locked = False
    Range("B1").Value = 0
ElseIf Range("A1").Value > Range("T14").Value + 3 Then
    Range("E16:T23").Locked = True
    Range("B1").Value = 1
End If


ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True
ActiveSheet.Protect Password:="VBAexpress"


End Sub

I really don't know what I'm doing. I got the second bit of code (the DrawingObjects line, and the True line) from using excel's 'record macro' option, but it doesn't work to allow comments. I have also searched these (and other) forums but did not find what I was looking for.

Help!

macro testing.xlsm

[大灰狼1976]

Hi poquist!
welcome to vbax forum.
I didn't confirm the code logic, maybe:

Private Sub Workbook_Open()
With Sheets(1)
  .Unprotect Password:="VBAexpress"
  If .Range("A1").Value <= .Range("S2").Value + 3 Then
    .Range("E4:S11").Locked = False
    .Range("B1").Value = 0
  ElseIf .Range("A1").Value > .Range("S2").Value + 3 Then
    .Range("E4:S11").Locked = True
    .Range("B1").Value = 1
  End If
  If .Range("A1").Value <= .Range("T14").Value + 3 Then
    .Range("E16:T23").Locked = False
    .Range("B1").Value = 0
  ElseIf Range("A1").Value > .Range("T14").Value + 3 Then
    .Range("E16:T23").Locked = True
    .Range("B1").Value = 1
  End If
  .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
  .Protect Password:="VBAexpress"
End With
End Sub

[poquist]

Hi! Thanks for responding to me. I finally got a chance to test your code and it did not allow the insertion of comments.

[Artik]

There is no guarantee that the second sheet in the workbook will not appear in the future, so it is not always the sheet1 that is active when the workbook is opened. Therefore, 大灰狼 1976 additionally used With...End With construction, which will guarantee the operation of the code in the right sheet.


As for the problem itself.
In code 大灰狼 1976, replace lines:

  .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
  .Protect Password:="VBAexpress"

into

.Protect Password:="VBAexpress", DrawingObjects:=False, Contents:=True, Scenarios:=True

Artik

[poquist]

Ah - thank you for explaining the change from activesheet to sheet1.

And SUPERB. Thank you, Artik!