Transfering Sheet Protection VBA

[redbaron06]

Hi all,

I am wondering if there is a way to transfer the sheet protection from one worksheet to another worksheet via VBA.

Currently, the user selects a name from a drop down list, it unhides a workbook of the same name, copies from that workbook, and places the copied information in an area of the sheet with the drop down list. I would like the protection settings (in this case, selectivly unlocked cells to allow users to easily tab through and not make changes to data that should not be changed) to be copied with this information. Is this possible?

Thanks in advance!

 

[Snakehips]

redbaron06,
Are you using a copy / paste special that excludes formats?

If you are just copy/ pasting the data over it should be taking the cell formats with it including the locked / unlocked status.

To actually invoke the locking you must add a line to your code after the paste something like...

ActiveSheet.Protect

or ActiveSheet.Protect Password :="yourpassword" if password protected.


If you do the above you will need to unprotect the sheet by code before your copy/ paste.

Hope that helps.

 

[redbaron06]

Thanks Snakehips! Got it to work following your advice, dont often work with protected worksheets, so I appreciate the help.

ActiveSheet.Unprotect
RangeTango = Range("O11").Value
Sheets(Range("O11").Value).Activate
Range("A1:D60").Select
ActiveSheet.Unprotect
Selection.Copy Destination:=Sheets("Main").Range("A1")
Sheets("Main").Activate
ActiveSheet.Protect