Auto lock sheet past a certain date

[Foxtrots]

Hi guys,

Not good at VBA, need some help here.

I have a workbook with multiple sheets, most of the sheets are locked (password protected) a few are not password protected as they are the input sheets for data.

I have a button which allows the user to lock the sheet when they have filled in data for the month. However, I would like to auto run a macro to lock each sheet at different dates to avoid inputs past a certain date.

Any help would be appreciated.

 

[PCosta87]

Hi,

I would suggest using some event to trigger the lock... for instance, when the user activates the sheet, if the current date is > than a specific date, the sheet would be password protected.
Upon opening is also an option... when the user opens the workbook, it checks the current date against a specific date and locks certain sheets.

If you need help with any of the above please post the sample file and I will gladly take a look at it.

 

[Foxtrots]

Dear Costa,

Thank you for the prompt reply.

I have attached a sample sheet. the first sheet is the input sheet and has a button to lock the sheet when the user has completed feeding data.

I still want to have a macro that's runs automatically upon opening the workbook and if it is past a certain date will lock the sheet avoiding further inputs.

The dates will differ as each input sheet is a monthly sheet.

Trust you can help and thanks in advance.

Kind Regards

 

[Belleke]

Perhaps something like this in ThisWorkBook (with your example code)

Private Sub Workbook_Open()
If Date >= "01/05/2017" Then Sheets("Apr Inp").Protect Password:="Abcd", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
If Date >= "01/06/2017" Then Sheets("May Inp").Protect Password:="Abcd", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub

 

[Foxtrots]

Hi Belleke,

Many thanks for your help, unfortunately the sheets are locked when I open the workbook, even though the date for locking is not triggered, I have attached the sheet and hope you can assist.

Regards

 

[Foxtrots]

Hi Belleke,

I figured out the problem, reversed the date format to yyyy/mm/dd and it works like a charm.

Thank you very much for your assistance.

Costa, thank you too.

Regards

 

[Belleke]

maybe you can try this if you want to keep the dd/mm/yyyy format in your code

If Date > CDate("05/06/2017")

See wat is does in excel with your local settings.

 

[Foxtrots]

Belleke,

Thanks for the option to change the date format, I have the sheet set to yyyy/mm/dd and works well.

Thank you for your assistance