Forum Discussion
automating locking of group of cells
This will help you, protects particular Sheet at particular TIME.
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("11:00:00"), "ProtectMysheets"
End SubWhere 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
- You may add as many Sheets & replace YourPassword with a Pass code of your choice, remember Pass code is Case sensitive.
- Suggested to protect entire sheet instead of TABLE since sheet has many Tables.
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
- Where Pass code abcd, Time 11:00:00 & Range for Table B2:F40 are editable.
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 🙂
Thank you!
- Noo,,, as soon you reach to the VBA editor,,, simply Copy & Paste any of the codes is required,,, and Excel will save it as STANDARD MODULE,, no need to Save as MODULE !! Do this select the Sheet where you want to apply the MACRO,, either press ALT+F11 or Right Click the Sheet Tab,,, and hit View Code,,, you find VB editor is now open,, the Copy & Paste any code,,, remember first two Macros are related to each other,, so Copy them together,,, 3 rd one is independent,, also Either use 1st & 2nd or ONLY 3rd,,, otherwise you get an Error since it will duplicate the WORKBOOK OPEN event,,, SAVE & Return to Sheet.. Close the Workbook. Now as soon you open the same Workbook,,, find the effects !!
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?
Check the attached Workbook has one sheet & Two tables, Table one has range A1:B10 is protected.
How it works:
- As soon you open this WB, Excel will prompt you to write Pass code, which abcd is small letters.
- Write PW & hit Ok.
- Now you find Table 1 , (Range A1:B10) is protected, you can't move MOUSE over the Range/Table.
- If you wanna to Unprotected the Table then hit REVIEW Tab then Unprotect Sheet command & write the PW (abcd).
- You may change the PW is in VBA code.
- To view the code hit either ALT+F11 or Right Click Sheet Tab ,, the View Code.
- Now check the Left Top of the Screen, you find the Project Explorer,, find & hit ThisWorkbook Icon.
- You may add more Range for other Tables and accordingly need to modify the VBA code.
N.B.
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.