poquist
07-02-2019, 09:25 AM
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! :)
24548
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! :)
24548