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/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.

"Chris" <Chris@discussions.microsoft.com> wrote in message 
news:4CAB91EA-0A25-4FD8-8ACB-B8CE340C3BBC@microsoft.com...
> 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
>