May 17 2021 04:36 PM
May 17 2021 04:36 PM
Hey Excel Nerds. I need to know if excel has this capacity. We have an excel workbook that has multiple sheets labeled as months and repeating tables on each sheet representing each day. Is there anyway that once a day is over, say midnight every night, that the table associated with that day gets locked automatically without someone manually having to lock it? I am looking to protect the sheet, but only for the parts that have passed in time since each table represents data from each day. historical data needs to be protected, which the currenty real-time day data can be entered by multiple people.
May 17 2021 11:07 PM
May 18 2021 02:58 PM
May 19 2021 03:15 AM
May 24 2021 04:41 PM
@Rajesh-S Hey! Thanks for the offer, I appreciate the help very much. I've attached a blank version of the workbook that need help with. Maybe you could use it to instruct me. Go to the Oct 2021 tab and see the tables at the very top that are labled. Each repeating row of tables represent a day in the month of October. Everyday data gets put into the empty cells by multiple people. At the end of the day at 11pm, I would like those tables to become locked for security reasons and continue to automatically lock as each day passes at 11pm. I've attached the workbook and a pdf showing exactly which group of cells (circled in red) that needs to be locked with each passing day. Let me know if there is a way to make this happen in VBA Macros. I am eager to have more knowledge under my belt, so again, much appreciated!
May 24 2021 10:45 PM
This will help you, protects particular Sheet at particular TIME.
Private Sub Workbook_Open() Application.OnTime Now + TimeValue("11:00:00"), "ProtectMysheets" End Sub
Where ProtectMysheets is also VBA macro.
This code has Sheet name along with Passcode.
Sub ProtectMysheets() Sheets("Sheet1").Protect Password:="YourPassword" Sheets("Sheet5").Protect Password:="YourPassword" End Sub
But if you are disparately looking to lock the Table then use the following VBA code, lock Range of cells, you need to use the cell range to lock particular Table.
Private Sub workbook_open() Dim systime As String Dim mypassword As String systime = Format(Now, "hh:mm:ss") mypassword = "abcd" If TimeValue(systime) > TimeValue("11:00:00") Then With ThisWorkbook.ActiveSheet .Unprotect Password:=mypassword .Range("B2:F40").Locked = True .Protect Password:=mypassword End With ThisWorkbook.Save End If End Sub
May 26 2021 05:54 PM
Hey! Thanks for the codes. I am going to attempt to make this happen with the last one you provided. I've been trying to put the code into a module, but nothing is happening and I can't seem to be able to run the macro. Am I doing something wrong? Should I be inputting this elsewhere? I'm selecting the sheet labeled MAY 2021 in the excel file, right-clicking and selecting View Code, which bring up the VBA window. see image.
Then I select Sheet 6(MAY 2021) from the project box and right click on INSERT to select MODULE. Once the module box pops up, I copy the code you provided with adjustments to the password and time as needed. After that, I don't know how to test it or run it to make it work.
Can you help me out? I have never worked with VBA Macro before
May 27 2021 12:38 AM
May 27 2021 03:51 PM
Rajesh-S, do I have to use military time format? I've tried using both options, 1&2 together as well as the 3rd code by itself and nothing is happening. If the locking works, then I shouldn't be able to delete any data in either the sheets I've chosen to lock or in the cells I've input in the RANGE, right? What am I missing?
May 28 2021 01:10 AM
Check the attached Workbook has one sheet & Two tables, Table one has range A1:B10 is protected.
How it works:
If you are using military Time then no need to modify the code, accordingly convert Military time to Standard since EXCEL by default considers Standard Time,,,, few are like 1300 hrs of 1 A M. 2400 for 12 mid night, 1930 for 07:30 PM, 031503 is 03:15:03 AM.