Transfering Sheet Protection VBA

redbaron06

New Member
Joined
Aug 6, 2010
Messages
44
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!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 6, 2010
Messages
44
Thanks Snakehips! Got it to work following your advice, dont often work with protected worksheets, so I appreciate the help.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,556
Members
414,155
Latest member
Grainne whiteside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top