PDA

View Full Version : [SOLVED] Protected cells - want to enable comments



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

大灰狼1976
07-05-2019, 01:49 AM
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
07-16-2019, 09:59 AM
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
07-16-2019, 03:07 PM
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
07-19-2019, 06:14 AM
Ah - thank you for explaining the change from activesheet to sheet1.

And SUPERB. Thank you, Artik!