+ Reply to Thread
Results 1 to 2 of 2

Copy Sheet With Protections of 1st Sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    04-14-2011
    Location
    Bay Area, CA
    MS-Off Ver
    Excel 2007
    Posts
    32

    Copy Sheet With Protections of 1st Sheet

    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:

    1. Select Unlocked Cells
    2. 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:

    ActiveSheet.Protect userinterfaceOnly:=True
    Which allows the VBA to make modifications to the locked cells.

    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
    Last edited by rbrookov; 05-11-2014 at 07:29 PM. Reason: Added list of options that I'm allowing end users to have control over

  2. #2
    Registered User
    Join Date
    04-14-2011
    Location
    Bay Area, CA
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Copy Sheet With Protections of 1st Sheet

    OK, so I think I might have figured it out...

    I added the following code to the "Activate Sheet" command in the "Workbook" module:

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveSheet.Protect userinterfaceOnly:=True
    End Sub
    I've not fully tested it thoroughly, but from a quick testing, it seems to be working.

    I wanted to post this in case anyone else is having a similar issue, that way it may help them as well.

    If this works 100% of the time after I'm done testing, I'll mark my own question as "solved" by my own solution - I wonder if I can give myself Reputation??

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-18-2014, 10:54 PM
  2. Lookup Value from Sheet 4 in Sheet 2, if found copy Sheet 2 Active Row to Sheet 5
    By lgosso23 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-11-2013, 02:51 PM
  3. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM
  4. Replies: 11
    Last Post: 10-14-2012, 01:03 PM
  5. Copying sheet protections?
    By Dhead97 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-02-2007, 02:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1