Search for variable in Column and Lock cell

[Sam11586]

Hi Everyone,

In my VBA code, I am looking to form a loop which can check the variables in Column A and for couple of variables in Column A unlock cell/cells from column D and For rest of the variables lock cell/cells from Column D & M.

Currently I have with error:

Private Sub Worksheet_Change()

Dim iRow As Long
Dim iLastRow As Long

iRow = 1
iLastRow = Bu1Calcsheet.cells(Rows.count, 1).End(xlUp).Row
Do While iRow<=iLastRow

If bu1CalcSheet.Cells(iRow, 1) Like "ABC" or bu1CalcSheet.Cells(iRow, 1) Like "XYZ" Then
bu1CalcSheet.Range("D" & iRow).Locked =False
bu1CalcSheet.Range("M" & iRow).Locked =False

ElseIf bu1CalcSheet.Cells(iRow, 1) Like "JKL" or bu1CalcSheet.Cells(iRow, 1) Like "DYU" or bu1CalcSheet.Cells(iRow, 1) Like "SPP" Then
bu1CalcSheet.Range("D" & iRow).Locked = True

End If
iRow=iRow+1
Loop
End Sub

Any idea?

 

[Joe4]

Welcome to the Board!

Which line of code is producing the error?
What is the error message you are getting?

Note I see that you appear to be using a worksheet variable named "Bu1Calcsheet", but I do not see it declared or its value set anywhere in your code.
Is the sheet you are running this against the same sheet? If so, there is no need for any sheet references. By default, the code will run against the sheet the code is stored in.

 

[Sam11586]

Welcome to the Board!

Which line of code is producing the error?
What is the error message you are getting?

Note I see that you appear to be using a worksheet variable named "Bu1Calcsheet", but I do not see it declared or its value set anywhere in your code.
Is the sheet you are running this against the same sheet? If so, there is no need for any sheet references. By default, the code will run against the sheet the code is stored in.

I am getting Run Time Error '1004'

Unable to set the Locked property of the Range Class

 

[Joe4]

Please answer my question about your worksheet reference.

 

[Sam11586]

Please answer my question about your worksheet reference.

Yeah, I have
set Bu1CalcSheet = Sheet(5)
set functionsSheet= Sheet(4)

 

[Joe4]

Yeah, I have
set Bu1CalcSheet = Sheet(5)
set functionsSheet= Sheet(4)

Where is this code?
It is not in the code you posted.

 

[Sam11586]

Where is this code?
It is not in the code you posted.

Plz see below code:

Private Sub Worksheet_Change()

Set bu1CalcSheet = Sheet(5)
Set functionsSheet= Sheet(4)

Dim iRow As Long
Dim iLastRow As Long

iRow = 1
iLastRow = Bu1Calcsheet.cells(Rows.count, 1).End(xlUp).Row
Do While iRow<=iLastRow

If bu1CalcSheet.Cells(iRow, 1) Like "ABC" or bu1CalcSheet.Cells(iRow, 1) Like "XYZ" Then
bu1CalcSheet.Range("D" & iRow).Locked =False
bu1CalcSheet.Range("M" & iRow).Locked =False

ElseIf bu1CalcSheet.Cells(iRow, 1) Like "JKL" or bu1CalcSheet.Cells(iRow, 1) Like "DYU" or bu1CalcSheet.Cells(iRow, 1) Like "SPP" Then
bu1CalcSheet.Range("D" & iRow).Locked = True

End If
iRow=iRow+1
Loop

Exit Sub

 

[Joe4]

What is the name of the module you have placed this VBA code in?

It is also good practice to declare all your variables, including the worksheet ones, before using them, i.e.

Dim iRow As Long
Dim iLastRow As Long
Dim bu1CalcSheet as Worksheet
Dim functionsSheet as Worksheet

Set bu1CalcSheet = Sheet(5)
Set functionsSheet= Sheet(4)

Then, all the references should match exactly.
If you have something like this one:

iLastRow = Bu1Calcsheet.cells(Rows.count, 1).End(xlUp).Row

note how the "B" in your code is capitalized, but the variable declaration has it lower case.
If you have declared your variables, like shown above, even if you type it capitalized, it should automatically make it match the case of the variable declaration.
If it does not, it is a sign that you misspelled it and have a typo!

 

[Sam11586]

What is the name of the module you have placed this VBA code in?

It is also good practice to declare all your variables, including the worksheet ones, before using them, i.e.

Dim iRow As Long
Dim iLastRow As Long
Dim bu1CalcSheet as Worksheet
Dim functionsSheet as Worksheet

Set bu1CalcSheet = Sheet(5)
Set functionsSheet= Sheet(4)

Then, all the references should match exactly.
If you have something like this one:

iLastRow = Bu1Calcsheet.cells(Rows.count, 1).End(xlUp).Row

note how the "B" in your code is capitalized, but the variable declaration has it lower case.
If you have declared your variables, like shown above, even if you type it capitalized, it should automatically make it match the case of the variable declaration.
If it does not, it is a sign that you misspelled it and have a typo!

module name: ReportCode

 

[Joe4]

module name: ReportCode

Worksheet_Change needs to go in one of the pre-defined Sheet modules in order to work automatically (specifically, the sheet you want it to run against), not one a General/Standard module.

The module should look something like:
SheetX (Sheet Name)
where
X is some number
Sheet Name is the name of the sheet.