Locking and unlocking columns based on date.

Cornhusker

New Member
Joined
Oct 20, 2010
Messages
19
My worksheet has a tab for each month with the name of the sheet the month and year (Feb 11, Mar 11, etc.)

In cells (D3:AH3) I have the day of the month.

I can't use an open event to lock cells because macros are not enabled when the file is opened.

Instead I want to use a close event to protect and lock the entire worksheet.

Then when the user opens the worksheet he/she will have to enable macros and then I'll need a macro to trigger that will unlock all of the columns except those that are from previous dates.

It goes the long way around but accomplishes what I want.

I would need the two seperate codes 1 to lock all of the cells on all the sheets with a close event.

another to unlock all the cells except the ones from previous days. Each sheet represents a different month and the range (d3:ah3) has 1-31 for the days of the month.

Thanks for the help
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
How do you know that the user will enable macros when they open the workbook? If they don't, your code would never fire - whether it is a close event or an open event or whatever....
 
Upvote 0
I don't know that they will enable macros, but if they don't then they won't be able to unlock any of the cells or unprotect the sheet. The second macro which I will trigger with a command button will unlock only the cells pertaining to the present day and the future. The rest will remain locked and protected. If they don't enable macros they won't be able to change anything on the file due to the close event that locks and protects everything.

This is a theory though and am not sure if there is a way around it. If there is a hole in my reasoning please advise.
 
Upvote 0
I see what you mean now. No I don't see a hole in your logic - but don't be confused by my post count I'm no expert :p

I do think that you need to use a before save event instead of before close. Because they could save, then close without saving. What about this in the workbook:

Code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    LockAll
End Sub

...and this in a module:

Code:
Option Explicit
Sub UnlockFutureDays()
 
    With Sheets(Format(Date, "mmm dd"))
        .Unprotect ''''''password:="???"
        .Range(.Columns(.Columns.Count), .Columns(CLng(Format(Date, "d")) + 3)).Locked = False
        .Protect '''''password:="???"
        'this will bring them to today's sheet even if they were not on it and select row 2's cells for today...
        .Select
        .Cells(2, CLng(Format(Date, "d")) + 3).Select
    End With
End Sub
Sub LockAll()
    Dim wkSht As Worksheet
    For Each wkSht In ThisWorkbook.Sheets
        wkSht.Unprotect '''''password:="???"
        wkSht.Cells.Locked = True
        wkSht.Protect '''''password:="???"
    Next
End Sub

...you can hook up the one macro to your button, and the other one triggers when they save. The only thing is, when they save then they will have to push your button again to keep editing things. Because when they save, it will lock everything even if they are not closing the book. Surely there is a way around that and maybe someone else will offer a method...

Edit: you should put your password into the code if the sheets are password-protected.
 
Upvote 0
Great catch on the before Save thanks. The lockall works perfectly.

I changed the unlock future days a bit

Code:
Sub UnlockFutureDays()
 
    With Sheets(Format(Date, "mmm dd"))
        .Unprotect Password:="Password"
        .Range(.Columns(.Columns.Count), .Columns(CLng(Format(Date, "d")) + 3)).Locked = False
        .Protect Password:="Password"
        
    End With
End Sub

I took out the selection of the cell. Is there a way to limit the unlocked columns on the current sheet stopping the unlocking at (AH) and still leaving (AI) and all subsequent columns locked?

I also need the macro to work on the other tabs. It work great on the "Feb 11" tab, but I want them to be able to unlock columns (D:AH) on any tab that refers to a month in the future.
 
Upvote 0
also is there a way to limit the format of the file when saving so that they cannot save the file as a regular excel document in an effort to circumvent the macros?
 
Upvote 0
To stop at AH, you can just change ".columns.count" (which chooses the far right column) to 34.

If they save the file as a regular Excel file, I don't see how that could allow them to circumvent the macros... wouldn't they be stuck with all the sheets locked in that case?

To make it unlock D:AH on all the sheets for months in the future, I'm not sure... I guess you would have to convert Jan:Dec into 1:12, then assess whether the sheet was future or not as you loop through the sheets...
 
Upvote 0
there is a cell (a2) in each sheet that has the month and year for that sheet. If you could use that to determine if the month of the tab is before or after the current month I could set the format of (a2) to whatever we need to get the macro to work. This cell is not in the range that is unlocked so I would be the only one that could modify it.
 
Upvote 0
Sorry for the delayed response... I'm sure there are shorter ways, but this seems to work:

Code:
Sub UnlockFutureDays()
Dim wkSht As Worksheet
    For Each wkSht In ThisWorkbook.Sheets
        With wkSht
            If .Range("A2").Value >= CDate(Month(Date) & "/1/" & Year(Date)) Then
                .Unprotect Password:="Password"
                If .Range("A2").Value = CDate(Month(Date) & "/1/" & Year(Date)) Then
                    .Range(.Columns(.Columns.Count), .Columns(CLng(Format(Date, "d")) + 3)).Locked = False
                Else
                    .Range("D:AH").Locked = False
                End If
                .Protect Password:="Password"
            End If
        End With
    Next
End Sub

...oh, in order for it to work, the entry in A2 has to be a date. Like, 1/1/2011; 2/1/2011; 3/1/2011, etc.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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
Back
Top