Increase cell value by 1 when workbook opens

[Gil]

Hello
I am using this code to increase a cell value by 1 when the workbook is opened


Option Explicit

Private Sub Workbook_Open()

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Sheet1").Range("f7").Value = Sheets("Sheet1").Range("f7").Value + 1

End Sub
All works well but I want to protect the cell to stop the number being changed.

[mikerickson]

Lock the (merged) cell and unprotect it before incrementing.


Private Sub Workbook_Open()

With Sheet1
.Unprotect
.Range("F7").Value = .Range("F7").Value + 1
.Range("F7").MergeArea.Locked = True
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterFaceOnly:= True
End With

End Sub

[Paul_Hossler]

Option Explicit

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Unprotect
.Range("F7:H14").Locked = True
.Range("F7").Value = .Range("F7").Value + 1
.EnableSelection = xlUnlockedCells
.Protect UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub




Edit -- but I like Mike's .MergeArea better than my hard-coded range

[Gil]

Hello Mike & Paul, I didn't expect such a quick reply & work has stopped me replying. Both solutions work well for me. Thank you both for your help.