Protect sheet except for input columns

[AlEsp08]

Hi! I need to protect the sheet except for specific columns where input is still needed based on the current month. Columns N to Y represents the months January to December. So, columns T to Y (July to December) need to be active because those months are still coming. When the month of August starts, input for July (column T) will automatically lock.

Also, try to avoid using the year in the formula because spreadsheet will be used for next year.

Thank you in advance!

 

[mse330]

Hey AlEsp08 & welcome to the board

I don't think you can achieve this task without VBA. If you wish to use VBA, you can place the below code in VBE under Microsoft Excel Objects > ThisWorkbook & it will check for this upon opening the file (of course macros have to enabled)

Private Sub Workbook_Open()

Dim Period As Byte
Period = Month(Date)

Sheets("Sheet1").Unprotect Password:="test" 
Columns("N:Y").Locked = True

Select Case Period
    Case Is = 1: Columns("N:Y").Locked = False
    Case Is = 2: Columns("O:Y").Locked = False
    Case Is = 3: Columns("P:Y").Locked = False
    Case Is = 4: Columns("Q:Y").Locked = False
    Case Is = 5: Columns("R:Y").Locked = False
    Case Is = 6: Columns("S:Y").Locked = False
    Case Is = 7: Columns("T:Y").Locked = False
    Case Is = 8: Columns("U:Y").Locked = False
    Case Is = 9: Columns("V:Y").Locked = False
    Case Is = 10: Columns("W:Y").Locked = False
    Case Is = 11: Columns("X:Y").Locked = False
    Case Is = 12: Columns("Y:Y").Locked = False
End Select
    
Sheets("Sheet1").Protect Password:="test"

End Sub

Let me know if this works out for you

 

[AlEsp08]

Thank you for the reply mse330! I've been using Excel for a long time but I've always avoid using VBA even if using a long formula but in this case I have no choice.

Some additional questions:
1. I have replaced "Sheet1" with the name of my sheet. Is this correct?
2. After putting in the code, should I go back to my sheet and click 'Protect Sheet' for this to work?
3. Do you mind to hand out your email address so I can send you the file I am working on?

 

[mse330]

Thank you for the reply mse330! I've been using Excel for a long time but I've always avoid using VBA even if using a long formula but in this case I have no choice.

Some additional questions:
1. I have replaced "Sheet1" with the name of my sheet. Is this correct?
2. After putting in the code, should I go back to my sheet and click 'Protect Sheet' for this to work?
3. Do you mind to hand out your email address so I can send you the file I am working on?

Yes, you are correct you need to change the "sheet1" with your sheet name ... I am already including in my code to protect the sheet afterwards so you don't have to do it manually.

I will PM you my email address shortly & you can send me the file