Having trouble changing the Locked status
 

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

Having trouble changing the Locked status
 

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

On Jun 24, 1:00*pm, Brad E. wrote:
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

Having trouble changing the Locked status
 

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?
--
Brad E.

Many thanks to the people answering questions. I wouldn''t even be
attempting some of the coding I have done without reading these posts.


" wrote:

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

On Jun 24, 1:00 pm, Brad E. wrote:
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

Having trouble changing the Locked status
 

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

On Jun 24, 2:46*pm, Brad E. wrote:
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?
--
Brad E.

Many thanks to the people answering questions. *I wouldn''t even be
attempting some of the coding I have done without reading these posts.



" wrote:
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

On Jun 24, 1:00 pm, Brad E. wrote:
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- Hide quoted text -

- Show quoted text -

Having trouble changing the Locked status
 

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?
--
Brad E.

Many thanks to the people answering questions. I wouldn''t even be
attempting some of the coding I have done without reading these posts.


" wrote:

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

On Jun 24, 2:46 pm, Brad E. wrote:
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?
--
Brad E.

Many thanks to the people answering questions. I wouldn''t even be
attempting some of the coding I have done without reading these posts.



" wrote:
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

On Jun 24, 1:00 pm, Brad E. wrote:
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- Hide quoted text -

- Show quoted text -

Having trouble changing the Locked status
 

Hi
Standard fix

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

stops the change event being triggered twice
regards
Paul


On Jun 24, 3:29*pm, Brad E. wrote:
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?
--
Brad E.

Many thanks to the people answering questions. *I wouldn''t even be
attempting some of the coding I have done without reading these posts.



" wrote:
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

On Jun 24, 2:46 pm, Brad E. wrote:
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?
--
Brad E.

Many thanks to the people answering questions. *I wouldn''t even be
attempting some of the coding I have done without reading these posts.

" wrote:
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

On Jun 24, 1:00 pm, Brad E. wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -