Having trouble changing the Locked status

[Brad E.]

I am getting an "Unable to set the Locked property of the Range class" error
at the last line of the code which I have copied here.

30 If Intersect(Target, Range("F5:F13")) Is Nothing Then GoTo 40
ActiveSheet.Unprotect Password:="mypass"
With Range(Target.Offset(0, 2), Target.Offset(0, 4))
.Validation.Delete
.Font.Color = 0
.Formula = "=VLOOKUP(N" & .Row & ",TABLES!$AF$4:$AG$32,2,FALSE)"
.Locked = True

The Merged cells in H5:J5 depend on the user-entry in F5. Most of the time,
the merged cells will be the vlookup formula, but in some instances, the
merged cells will need to be unlocked and user-entered. I am setting the VBA
up to put the formula in EACH time and (in the code directly following) if
the few instances occur, I will set validation, change the font color, clear
the formula and unlock the range.

I appreciate any help. Brad

 

[paul.robinson]

Hi
I don't get a problem. Tested with cells merged and unmerged. Tested
with Target being single and multiple cells. Did you put in the End
With bit?!
regards
Paul

 

[Brad E.]

Thanks Paul, for the reply.

I have included the End With. I actually am thinking the Locked property
cannot be changed because the Worksheet doesn't unprotect. The Macro runs up
to the ".Locked = True" command, which means the worksheet should be
unprotected. But I have to manually unprotect the worksheet to access locked
cells. Can you see any mistakes in my Unprotect line?

 

[paul.robinson]

Hi
Protection bit works OK for me. Is your password right - very easy to
put in a capital or a leading/trailing white space?
regards
Paul

 

[Brad E.]

I have this code in the Worksheet_Change event. When it runs because I am
changing a value in F5:F13, this in turn changes values in H5:J13, which I
believe re-runs the macro because of the original run making a worksheet
change happen.

If I switched this to the Worksheet_SelectionChange event, would I get
around the looping of the macro? In other words, would it only run when a
user changes a cell entry, and not when a macro changes the cell entry?

 

[paul.robinson]

Hi
Standard fix

Application.EnableEvents = False
'your code
Application.EnableEvents = True

stops the change event being triggered twice
regards
Paul