Howdy,
The other day I posted a question in this thread about copying a Worksheet & renaming it based off of a pull-down:
http://www.excelforum.com/excel-prog...nite-loop.html
To which AlphaFrog so graciously & expertly helped me to solve! The code works BEAUTIFULLY, and does EXACTLY what I had requested!!
The first sheet I set up to be protected such that the end user could ONLY select / edit certain cells, while all others are locked. I did this by selecting the cells I wanted the end user to edit and "unlocked" them, then I chose the "Protect Sheet" icon within the "Review" tab, and chose only the options I wanted my end users to be able to modify. The options I chose were:
- Select Unlocked Cells
- Format Rows (this is because I have code to show / hide certain rows based on a checkbox)
I also added in this code to the Workbook Open section:
Which allows the VBA to make modifications to the locked cells.![]()
ActiveSheet.Protect userinterfaceOnly:=True
However, I've noticed that when I choose new "Suppliers" (which invokes the copy & rename macro, the link being posted above), the new sheet(s) loses all protection.
I would like for any new sheets to ALSO be protected such that the user can ONLY select / edit the cells I want them to, and NO other cells.
So, my question is - what do I need to add to ensure that any new sheets being copied retain the protection of the first sheet, and where do I put it (ie, "ThisWorkbook" etc)?
Thanks in advance (you guys have helped my VBA skills grow immensely!!)
Rob
Bookmarks