PDA

View Full Version : Excel will lock specific area based on a date



michal2287
03-28-2017, 03:22 AM
Hi All,

I am now using an Excel sheet for reporting hours, that locks automatically a specific area based on a week. E.g. - the specific area in January will be locked at the end of week 4, but the rest of the year will remain open. After week 8, the area between week 4 and 8 will lock as well, so at the end the week 1 - 8 will be locked and the rest will remain open. It goes like that till the end of the year.

I would like to ask, if somebody could help me with the script, that the whole workbook would actually lock on specific date (after we bill the hours), not based on the week?

E.g. January billing date was on 28th of January. So at the end of 28th the excel would lock the selected area.
In february, the billing date was on 24th, so at the end of that day, Excel would keep the area from January locked + would lock the area for February.


Option ExplicitPrivate Sub Workbook_Open()
Dim iWeekNum As Long, iColNum As Long
Dim ws As Worksheet



iWeekNum = Application.WorksheetFunction.WeekNum(Now, 1)


If Weekday(DateSerial(Year(Now), 2, 2)) > vbWednesday Then
iWeekNum = iWeekNum - 1
End If

iColNum = iWeekNum + 4


For Each ws In ActiveWorkbook.Worksheets
With ws
If .Range("D2").Value = "Week 1" Then
.Unprotect Password:="heslo"
.Cells.Locked = True
.Columns(iColNum).Locked = False
.Columns(iColNum - 1).Locked = False
.Columns(iColNum - 2).Locked = False
.Columns(iColNum + 1).Locked = False


.Range("A3:A19").Locked = False
.Range("A1:B1").Locked = False
.Range("A2:B2").Locked = False
.Range("A25:B25").Locked = False


.Range("D2:BD2").Locked = True
.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
.EnableSelection = xlUnlockedCells
End If
End With
Next
End Sub

Thank you for any kind of help :)

Michal