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...
I now need to add something similar for rows - I am guessing along the lines of
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.
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.