I'm presuming your title change is acceptable since it now depicts your need accurately.
I am also presuming the sheet you've uploaded is a "template" that must be initialized each week, thus your issue with when it is opened. Adding in a macro will require your users to enable macros on their system or at least when they open the sheet.
So, if this IS a template, first...UNLOCK cell B1 so that it is editable, clear the cell so that it is empty....then lock your sheet.
On the Menu Bar just to the left of FILE : EDIT : VIEW : INSERT you will see an Excel icon. Right-click on it and select VIEW CODE. This will open the WorkBook Module for you.
Now, paste in this code:
Private Sub workbook_open()
If IsEmpty(Sheets("Bar Business").Range("B1")) Then
Range("B1").FormulaR1C1 = "=TODAY()-WEEKDAY(TODAY(),3)"
Range("B1").Value = Range("B1").Value
Range("B1").Locked = True
MsgBox "This sheet is now locked into week starting " & Range("B1").Value
End If
End Sub
This macro will see the empty cell, add the commencement date, convert it to a real value, and lock the cell so it is no longer changeable.
Bookmarks