0

I currently have a worksheet that, upon activation, unlocks a portion of the worksheet for user entry, but protects the rest of the worksheet. When opening the worksheet, there is a noticeable delay before becoming active for the user. Is there a way to use an array for this? 

Dim ws As Worksheet
Dim rng As Range
Dim ce As Range

Set ws = ActiveSheet
Set rng = Union(ws.Range("B4:B9"), ws.Range("E4:E9"), ws.Range("I4:I10"), ws.Range("B10"), ws.Range("A14:O425"))

Application.ScreenUpdating = False
ws.Unprotect Password:="mediate"
For Each ce In rng
    ce.Select
    Selection.Locked = "False"
Next ce

ws.Protect Password:="mediate", UserInterfaceOnly:=True, AllowFormattingCells:=True
Application.ScreenUpdating = True
TylerH
  • 20,799
  • 66
  • 75
  • 101
Decoy26
  • 89
  • 13
  • 1
    You shouldn't need to lock & unlock the cells every time. Leave them marked as LOCKED, because that setting will only have an effect when the sheet is protected, and will be ignored if the sheet is Unprotected. – ashleedawg Jan 04 '18 at 14:07
  • 1
    Don't do a For Loop, just do `rng.Locked = "False"`, and yes you don't need to unprotect the sheet just to unlock some cells. Do that in the beginning and you never need to again. – tigeravatar Jan 04 '18 at 14:08
  • Don't use select for one. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Apart from that, you can set the properties of the entire range at once, instead of looping over it, as @tigeravatar says. – eirikdaude Jan 04 '18 at 14:10
  • Read the documentation for [Worksheet.Protect Method](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-protect-method-excel) – ashleedawg Jan 04 '18 at 14:10
  • so... who's going to write that up in an answer? ;) – CallumDA Jan 04 '18 at 14:10
  • @tigeravatar, Using your rng.Locked = "False" leads to a Run-time Error '1004': Unable to set the Locked Property of the Range Class. – Decoy26 Jan 04 '18 at 14:12
  • @Decoy26 Try not putting false in quotation marks. This test worked fine for me: https://pastebin.com/UBQFk8s2 – eirikdaude Jan 04 '18 at 14:15
  • @eirikdaude, taking off the quotes leaves me with the same error. – Decoy26 Jan 04 '18 at 14:18

1 Answers1

1

Using Range.Locked = False works, but there was an additional mistake that I made that caused the errors I described. I split each range out to use .Locked = False and figured out which ranges were being problematic. Some of the ranges have merged cells, and I did not specify the entire 'length' of the range. I.E. Range("E4:E9") should have been Range("E4:F9").

Here is the working code:

Dim ws As Worksheet
Dim rng As Range

Set ws = Worksheets("Initial Entry")
Set rng = Union(ws.Range("B4:B9"), ws.Range("E4:F9"), ws.Range("I4:K10"), ws.Range("B10:F10"), ws.Range("A14:O425"))

rng.Locked = False

ws.Protect Password:="mediate", UserInterfaceOnly:=True, AllowFormattingCells:=True
TylerH
  • 20,799
  • 66
  • 75
  • 101
Decoy26
  • 89
  • 13