Unprotect column depending on day of month

brianfosterblack

Board Regular
Joined
Nov 1, 2011
Messages
189
I have a password protected worksheet which when I open enters the day of the month in cell c1 (This is a formula in cell C 1 which just gives the date by today but showing the day only)
I then have columns where in row 2 I have the column header for each day of the month (1 - 31)
When I open the worksheet I want to unprotect only the column for the current day of the month
This is so the employee can enter information in the column for today but cannot go back and change historical information.
When I leave the sheet it must protect everything on the sheet again.
Can you help with this code.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
Try this in the sheet's module:
VBA Code:
Private Sub Worksheet_Activate()
    Dim r As Range
    Set r = Range("2:2").Find(Range("C1").Value, , xlValues, xlWhole)
    Me.Unprotect Password:="1234"
    r.EntireColumn.Locked = False
    Me.Protect Password:="1234"
End Sub

Private Sub Worksheet_Deactivate()
    Me.Unprotect Password:="1234"
    Cells.Locked = True
    Me.Protect Password:="1234"
End Sub
Anyway,
I have a password protected worksheet which when I open enters the day of the month in cell c1 (This is a formula in cell C 1 which just gives the date by today but showing the day only)
This is done not by a code but by a formula like below right?
Excel Formula:
=DAY(TODAY())
 

brianfosterblack

Board Regular
Joined
Nov 1, 2011
Messages
189
Try this in the sheet's module:
VBA Code:
Private Sub Worksheet_Activate()
    Dim r As Range
    Set r = Range("2:2").Find(Range("C1").Value, , xlValues, xlWhole)
    Me.Unprotect Password:="1234"
    r.EntireColumn.Locked = False
    Me.Protect Password:="1234"
End Sub

Private Sub Worksheet_Deactivate()
    Me.Unprotect Password:="1234"
    Cells.Locked = True
    Me.Protect Password:="1234"
End Sub
Anyway,

This is done not by a code but by a formula like below right?
Excel Formula:
=DAY(TODAY())
Thanks this works perfectly. The day is provided by a formula in C1 as you show it above
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,723
Members
417,107
Latest member
derekMG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top