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

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Not certain what you are asking, but could it be this?

Intersect(cell.EntireRow, Columns("D:F")).Locked = True

 

[DavidG007]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Not certain what you are asking, but could it be this?

Intersect(cell.EntireRow, Columns("D:F")).Locked = True

thanks Peter for your reply, really appreciated.

I've attached a screenshot to hopefully explain better, but, basically what i am trying to achieve is;

* user selects a value in column B
* based on that value the user needs to complete cells in column C:E or F:H with the non required cells being locked

* in each of the columns are named range drop down lists.

sounds easy (and probably is!) but been at this for quite a while and driving me barmy lol,

Really appreciate your help and thank you

 

[Peter_SSs]

I'm still not sure that I have understood, but is this it?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Selection.CountLarge = 1 And Not Intersect(Target, Range("B4:B18")) Is Nothing Then
    ActiveSheet.Unprotect "Password"
    With Intersect(Target.EntireRow, Columns("C:H"))
      .Locked = False
      Select Case Target.Value
        Case "Expansion"
          .Cells(, 4).Resize(, 3).Locked = True
        Case "SIB"
          .Cells(, 1).Resize(, 3).Locked = True
      End Select
    End With
    ActiveSheet.Protect "Password"
  End If
End Sub

 

[DavidG007]

Hi Peter,

Still not quite right, I've attached another screenshot to hopefully help. Sorry to be a pain, thanks

 

[Peter_SSs]

I can't tell much from an image nor can I copy from it. Consider XL2BB
In what way was it "not quite right"?

Also, please remember the first paragraph from post #2

 

[DavidG007]

just sent you a PM Peter as struggling with the xl2bb add-in ,
Basically, the current code is protecting all the cells and not allowing the input / locking per row based on the selection in column B.

Thanks

 

[Peter_SSs]

Basically, the current code is protecting all the cells and not allowing the input / locking per row based on the selection in column B.

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

 

[DavidG007]

User Selection
Extension	User Input	User Input	User Input	NO INPUT _ LOCK	NO INPUT - LOCK	NO INPUT - LOCK
SIB	NO INPUT - LOCK	NO INPUT - LOCK	NO INPUT - LOCK	User Input	User Input	User Input
etc
etc

each row will have a different set of cells that will be locked etc based on what is selected in column B.

 

[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

Yes, that is exactly what should happen. How strange, let me just double check.