VBA code to lock / unlock a column depending on cell content within the column

[LPS ESQ]

Hi there,

I am being particularly thick today (Thicker than a Whale Omelette thick) , and I cannot work out the obvious despite staring at it for ages, so I am hoping one of you lovely Ladies or Gents can possibly help me.

Background:

I have created a template spreadsheet for people to fill in "actuals" post month end for submission back to me by working day x. The spreadsheet has columns for months and the rows are for specific criteria. At most per department 36 cols of data and 50 rows - so minute on the excel spreadsheet scale.

What I want to do is for say April Data have a window of entry from say 27/4 to 4/5 for completion of data. Outside those dates the column will be locked. Previously some people may have "alledgedly" changed previous months figures and I want to stop this.

I've completed all the date open formulae etc and based upon todays date, open and close date have if statements that will give an output of "FT","TT" or "TF" - Info is on a very hidden sheet that unhides upon opening, copies data across and then hides again.

The code I have so far for the unlock/lock columns is here. It doesn't work and I am being stupid.

Sub columnlock()


Dim strPassword As String
Dim x As String

Sheet1.Select
ActiveSheet.Unprotect Password:="hello"
Cells.Locked = False

For Each Value In Range("M2:AJ2").Cells
    If Value = "TF" Then
        EntireColumn.Locked = True
    End If
Next

'ActiveSheet.Protect Password:="hello"


End Sub

Many thanks

Lynton

 

[Max1616]

Hi Lynton, easy fix for you! You need to add "Value." in front of EntireColumn:

Sub columnlock()

Dim mycell As Range
Dim strPassword As String
Dim x As String
Dim Value As Range

Sheet1.Select
ActiveSheet.Unprotect Password:="hello"
Cells.Locked = False

For Each Value In Range("M2:AJ2").Cells
    If Value = "TF" Then
        Value.EntireColumn.Locked = True
    End If
Next

ActiveSheet.Protect Password:="hello"

End Sub

I also added the Dim for your "Value" Variable, even though it's unnecessary, it's good form.

Let me know if this doesn't work!

Good luck!

 

[Max1616]

It's worth mentioning that the code is case sensitive, so this will not fire off for "tf"

 

[LPS ESQ]

Max - that works perfectly. Thankyou very much for your help and assistance. Talk about missing the obvious on my part!!!! Cheers!