VBA Lock then Copy question

[Pinaceous]

Hi All,

I'm trying to figure out a way for a button click to lock a specified range of cells Then to Copy other specified cells.


I'm working with the following:

 Dim response As VbMsgBoxResult
response = MsgBox("ONCE YOU SELECT THIS BUTTON:                                                                                                                                                                                                                                      IT WILL COPY THE INFORMATION AND LOCK THE SHEET!!!                                                                                                                                                                                       DO YOU WANT TO CONTINUE?", vbYesNo, "ProjectUpdate!")
  

Range("B11:U180").Select
  
  ActiveSheet.Protect Password:="[EMAIL="G@cvXpcpSp96"]PASSWORD[/EMAIL]", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True
  
  
Worksheets(2).Range("C182:D182").Copy

Exit Sub
   If response = vbNo Then
    MsgBox "            PLEASE MAKE SURE THAT Your info is UP-TO-DATE!", vbCritical, "ProtectUpdate!"
End If
    End Select
       
Exit Sub
End Sub

If you run this, you'll see what I'm talking about. It won't lock the specified range and then it won't copy the other range??


I can't seem to figure this one out.

Anyone have any suggestions on how to write this code??

Thanks,
Pinaceous

 

[Smitty]

For the first part, you haven't locked the cells, just selected them. Try:

Range("B11:U180").Locked = True

For the second, you copied, but didn't specify a destination. Try:

Worksheets(2).Range("C182:D182").Copy Destination:=Sheets(1).Range("A1")

Change the Destination as needed. Note that "Destination:=" isn't required, I just put it there for clarity.

HTH

 

[sericom]

Just change

Range("B11:U180").Select

to

Range("B11:U180").Locked = True

You'll probably want to unlock all the cells first though.


You'll need to specify a destination for your copy, eg.

Worksheets(2).Range("C182:D182").Copy Worksheets(1).Range("A1")

 

[Pinaceous]

Hello everyone,

Thanks for cleaning up a few of those issues.

I still have a question.

What if I don't specify a destination for the paste and just have it highlight those specified cells for the user to select its destination independently from the sub. Is this possible?

I know it is possible when it stands alone, for example:

Sub Copy() Worksheets(2).Range("C182:D182").Copy End Sub

Any more helpful hints??

Thank you both for responding!

Pinaceous

 

[Pinaceous]

Also, how could I password lock my original proposed selection??

Thanks

 

[Smitty]

What if I don't specify a destination for the paste and just have it highlight those specified cells for the user to select its destination independently from the sub. Is this possible?

That could be complicated, especially if the destination isn't on the same sheet as the copy range. I'd let/ask the user to select the range first, then feed it to the destination. Take a look at the Application.InputBox method in the helpfile for that.

 

[Smitty]

Also, how could I password lock my original proposed selection??

Thanks

You don't. You protect a sheet in it's entirety, that's why you select certain cells to be locked/unlocked.