VBA to selectt a variable range of cells to protect

[glynn1969]

Hello

I am looking for a vba code to allow a user to enter the value for the range of cells to protect on a worksheet.

Ideally i would like it that when i run the macro then a message box would pop up to ask the user to enter the cell range values then to confirm and the cell range to be protected.

EG the mesage would ask for the cells - i would enter b2 and d9 the the range B2:D9 would be protected with the remaining worksheet not protected.

Hope someone can help.

Thank you

 

[Rick Rothstein]

Give this code a try (it allows you to type the range in, for example, B2:D9, or to select the range on the sheet by left-click dragging the mouse)...

[table="width: 500"]
[tr]
	[td]Sub ProtectRange()
  Dim Rng As Range
  ActiveSheet.Unprotect
  Set Rng = Application.InputBox("Type or select the range to protect:", Type:=8)
  Cells.Locked = False
  Rng.Locked = True
  ActiveSheet.Protect
End Sub[/td]
[/tr]
[/table]

 

[glynn1969]

That works fantastic.
Just one more thing is is possible for the inpu to be made in two separate entries
eg first enter start of range, such as B2.

Then a second box for the end of the range, such as D9.

No problem if this is difficult as you have solved my issue with a lovely macro.

Thank you

 

[glynn1969]

Is it posible to apply the same range across two or more worksheets at once with this macro.

 

[Rick Rothstein]

That works fantastic.
Just one more thing is is possible for the input to be made in two separate entries
eg first enter start of range, such as B2.

Then a second box for the end of the range, such as D9.

Sure, no problem. I did not give you this at first because I thought it more cumbersome for the user (you have to type or select a cells, then say OK, then do it again for the second cell)... I figured since you knew both cells, why not enter or select everything at once. But if you want the two step procedure, here is the code for it (note the name change... you can change it back if you want)...

[table="width: 500"]
[tr]
	[td]Sub ProtectRange2()
  Dim TL As Range, BR As Range
  ActiveSheet.Unprotect
  Set TL = Application.InputBox("Enter or select the top left cell of range to protect:", Type:=8)
  Set BR = Application.InputBox("Enter or select the bottom right cell of range to protect:", Type:=8)
  Cells.Locked = False
  Range(TL, BR).Locked = True
  ActiveSheet.Protect
End Sub[/td]
[/tr]
[/table]

 

[glynn1969]

Thank you very much for this.
I would post a like/thank you to your profile but cant see how to...will keep searching

 

[Fluff]

I would post a like/thank you to your profile but cant see how to...will keep searching

Look at the bottom right of each post & you will see "Thank you for posting this" & "Like this post"
You can click on them to show your appreciation.

 

[glynn1969]

Thanks ----- was bottom left for me.

 

[Fluff]

I can't even tell my left from my right!!

 

[Rick Rothstein]

I can't even tell my left from my right!!

Don't feel too bad about this... it used to be on the bottom right before the forum was revamped.