VBA coding to prevent entry into various cells....

[LPS ESQ]

Hello All,

I used this forum a couple of weeks ago regarding a macro that would only allow data entry into various columns depending on the date - of which I was havinga couple of syntax issues, which was very helpfully and quickly answered.

I now want to take this one step further.

The spreadsheet has columns for months and groups of rows for the departments. The first 20 lines or so are generic for all departments and then there are between 1 and 10 extra lines for each department. Department is selected by a drop down valuebox and conditional formatting highlights what needs to be filled in.

The following code is in place which prevents typing in other months...

Sub columnlock()

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


Sheet1.Select
ActiveSheet.Unprotect Password:="hello"
Sheet2.Visible = xlSheetHidden


Cells.Locked = False

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


Next

Sheet2.Visible = xlSheetVeryHidden
ActiveSheet.Protect Password:="hello"

End Sub

I now need to add something similar for rows - I am guessing along the lines of

For Each Value In Range("A40:A120").Cells
    If Value <> "TT" Then
        Value.EntireColumn.Locked = True
        End If

If I try this I get all kinds of error codes popping up which generally seem to be having 2 x "For" conditions. I guess I need to nest the conditions somehow, but have no idea as to how. Could anyone please point in the right direction?

Many thanks in advance.

 

[Alex Blakenburg]

You are not showing where you are positioning the Row locking code in the main body, I suspect you might have it before the Next line and it needs to be after the Next line.
Also
- your row snipped does not have a Next Value line
- I am assuming EntireColumn should be EntireRow

Note: I would not be in favour of using Value as a variable name since it is a keyword in VBA. Also the name itself gives no clue that it is a range object.

I would think the 2 "For" statements should be positioned as per the below.

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

For Each Value In Range("A40:A120").Cells
    If Value <> "TT" Then
        Value.EntireRow.Locked = True
        End If
Next Value