Forum Discussion

maker340's avatar
maker340
Copper Contributor
May 17, 2021

automating locking of group of cells

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 any...
excel
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
May 18, 2021
This needs VBA macro,,, if you are comfortable then edit your post and add new line as well the Tag VBA to the post !!
maker340's avatar
maker340
Copper Contributor
May 18, 2021
Hey Rajesh-S! Thanks for the response. I watched some videos on VBA Macro. I don't think I'm equipped to figure it out and make it do what I need. Can you advise about where I can get outside help on this? Much appreciated!
  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    May 19, 2021
    If possible attach the WB having the sheet has Group of Cells then I'll show you how to work with VBA macro !!
    • maker340's avatar
      maker340
      Copper Contributor
      May 24, 2021

      Rajesh_Sinha  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!

      • Rajesh_Sinha's avatar
        Rajesh_Sinha
        Iron Contributor
        May 25, 2021

        maker340 

         

        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

         

        • 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.

         

Resources