VBA Dynamic Row Range

[DavidG007]

Hi, Can anyone please help?

I need to amend the code so that the Row Range selection is dynamic, as per below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Unprotect "Password"

For Each cell In Range("B4:B18")

If cell.Value = "Expansion" Then

'NEED TO AMEND THIS LINE SO THAT THE ROW RANGE SELECTION IS DYNAMIC BASED ON THE ROW NUMBER.  RANGE SAY D:F, ROW = VARIABLE.
cell.EntireRow.Locked = True

End If

Next cell

ActiveSheet.Protect "Password"

End Sub

Big thanks

 

[DavidG007]

just checking;
* code is copied to Module1
* worksheet - all cells locked apart from column B
* worksheet protected.

Then, User makes selections?

 

[DavidG007]

It is not for me. In my sheet below if I select B7 then I can enter/edit values in C7:E7 but not F7:H7.
If I select B9 I an enter in F9:H9 but not C9:E9
Is that what should be happening? If not please explain what should be happening when I select each of those two cells.

22 02 22.xlsm
	B	C	D	E	F	G	H
1
2
3
4
5
6
7	Expansion
8
9	SIB
selection change lock

for some reason it doesn't work for me!?

 

[Peter_SSs]

* code is copied to Module1

No, the code should go in the particular worksheet's module. Since you already had Worksheet_SelectionChange code I assumed you had it in the correct place.

 

[Peter_SSs]

The worksheet I am testing on is called 'selection change lock' so my code goes here:

 

[DavidG007]

very strange, it is still allowing me to enter in the cells that should be locked?

 

[Peter_SSs]

Can you upload a copy of the file with any sensitive data disguised or removed to DropBox, OneDrive, Google Drive etc and post a shared link here?
Also give some details of exactly what cell you selected and what cell(s) you were able to enter into that should have been disallowed.

 

[DavidG007]

sent to your inbox

 

[Peter_SSs]

sent to your inbox

As I stated above & In the conversation, any links to uploaded files need to be publicly shared here in the actual forum. See #4 of the Forum Rules

 

[DavidG007]

HI Peter, apologies, please see link below;

Copy of Attribute_Validation_Example v2.xlsm

drive.google.com

 

[Peter_SSs]

I am not seeing what appears to be your main problem**.

If I first select B5 then I can move to C5, D5 and/or E5 and choose a drop-down value. I am unable to make a selection in F5, G5 or H5
If I first select B11 then I can move to C5, D5 and/or E5 and am prevented from choosing a drop-down value. I am able to make selections in F5, G5 and H5

What happens to you in that file if you follow step-by-step what I described above?

** The one area where my code may be doing something that you don't want is that if I select a 'B' cell with no value (say B6) then my code is unlocking that row so entries could be made in any of C6:H6. That will be easy enough to correct if you don't want that, but not much point yet if the main objective of the code is not working.