macro to unlock and lock cells in password protected sheet

[Chris]

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

 

[Mike H]

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.

 

[Jim Thomlinson]

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.

 

[Rob]

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

http://spreadsheetpage.com/index.php/tip/spreadsheet_protection_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.