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

LPS ESQ

New Member
Joined
Feb 18, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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...

VBA Code:
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

VBA Code:
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,214,965
Messages
6,122,499
Members
449,089
Latest member
Raviguru

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