Hi all

Is there any way I can get a macro to unlock cell, paste valuse and the lock
again after completion. I have a macro to transfer valuse from one rang to
another. The macro will work ok if I leave the input cells unlocked. I would
like to be able to lock these cells so they can't be changed without the the
use of the macro. The input cells are F23:F23. See Code Below.
Regards
Chris
Sub update()



Worksheets(13).Range("I20:I23").Copy
Worksheets(13).Range("F20:23").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False




End Sub

Hi,

Mabe this. Note the password is case sensitive

Sub update()
With Worksheets(13)
.Unprotect Password:="Mypass"
.Range("I20:I23").Copy
.Range("F20:F23").PasteSpecial Paste:=xlPasteValues
.Protect Password:="Mypass"
End With
Application.CutCopyMode = False
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Chris" wrote:

Hi all

Is there any way I can get a macro to unlock cell, paste valuse and the lock
again after completion. I have a macro to transfer valuse from one rang to
another. The macro will work ok if I leave the input cells unlocked. I would
like to be able to lock these cells so they can't be changed without the the
use of the macro. The input cells are F23:F23. See Code Below.
Regards
Chris
Sub update()



Worksheets(13).Range("I20:I23").Copy
Worksheets(13).Range("F20:23").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False




End Sub

Or...

Sub update()
With Worksheets(13)
.Unprotect Password:="Mypass"
.Range("F20:F23").Value = .Range("I20:I23").Value
.Protect Password:="Mypass"
End With
End Sub

Note to the OP. Refering to a shee by it's index number is generally
speaking a bad idea. Adding or deleelting worksheets can change that number
and then your macro will operate on the wrong sheet. Once executed there is
no undo for a macro.

--
HTH...

Jim Thomlinson


"Mike H" wrote:

Hi,

Mabe this. Note the password is case sensitive

Sub update()
With Worksheets(13)
.Unprotect Password:="Mypass"
.Range("I20:I23").Copy
.Range("F20:F23").PasteSpecial Paste:=xlPasteValues
.Protect Password:="Mypass"
End With
Application.CutCopyMode = False
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Chris" wrote:

Hi all

Is there any way I can get a macro to unlock cell, paste valuse and the lock
again after completion. I have a macro to transfer valuse from one rang to
another. The macro will work ok if I leave the input cells unlocked. I would
like to be able to lock these cells so they can't be changed without the the
use of the macro. The input cells are F23:F23. See Code Below.
Regards
Chris
Sub update()



Worksheets(13).Range("I20:I23").Copy
Worksheets(13).Range("F20:23").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False




End Sub

Hi,
The below link has a way to achieve what you want. HTHs Rob

http://spreadsheetpage.com/index.php...otection_faq1/
Can I set things up so my VBA macro can make changes to Locked cells on a
protected sheet?
Yes, you can write a macro that protects the worksheet, but still allows
changes via macro code. The trick is to protect the sheet with the
UserInterfaceOnly parameter. Here's an example:

ActiveSheet.Protect UserInterfaceOnly:=True
After this statement is executed, the worksheet is protected -- but your VBA
code will still be able to make changes to locked cells and perform other
operation that are not possible on a protected worksheet.

"Chris" wrote in message
...
Hi all

Is there any way I can get a macro to unlock cell, paste valuse and the
lock
again after completion. I have a macro to transfer valuse from one rang to
another. The macro will work ok if I leave the input cells unlocked. I
would
like to be able to lock these cells so they can't be changed without the
the
use of the macro. The input cells are F23:F23. See Code Below.
Regards
Chris
Sub update()



Worksheets(13).Range("I20:I23").Copy
Worksheets(13).Range("F20:23").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False




End Sub