+ Reply to Thread
Results 1 to 6 of 6

Macro for Protecting Sheet (with same name) on multiple files at once?

  1. #1
    Registered User
    Join Date
    08-10-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    16

    Question Macro for Protecting Sheet (with same name) on multiple files at once?

    Hello Excel Geniuses!

    I have received so much help from this community I hope someone can help me with this request.
    I have a macro that duplicates a excel file into how ever many files I need... so If I need 150 copies of the the file, it will make it 150 times.

    However, each file the first tab called "Clients" has information that I want protected so users can't alter it.
    I can't protect it before I run the duplication because the data is added by a group of people and I need it unprotected until they add the information.

    What I am looking for is a macro that I can click and run, allows me to select multiple files, then it will automatically Protect Sheet 1 "Clients" on all the files I select so I don't have to open all 150 files after data is entered and Protect sheet 1 manually.

    Anyone know how to do something like this?
    I would greatly appreciate your help!!!
    Last edited by brolsen; 07-19-2014 at 03:34 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro for Protecting Sheet (with same name) on multiple files at once?

    Even with code you'll still need to open all the files to protect them or protect the sheets in them.
    If posting code please use code tags, see here.

  3. #3
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Macro for Protecting Sheet (with same name) on multiple files at once?

    Once the file is opened, you can protect the sheet with code like the following:
    Please Login or Register  to view this content.
    Lewis

  4. #4
    Registered User
    Join Date
    08-10-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Macro for Protecting Sheet (with same name) on multiple files at once?

    Right, I guess I am looking for a code that will open all the files I select one at a time, protect sheet 1 (Clients), close the file, then move to the next one.
    I have a "Combine" macro that does something similar to this... it allows me to select multiple files, it opens them one at a time, copies and pastes data from them into a "Master" sheet and then closes them.
    This is that code:
    Please Login or Register  to view this content.
    I guess I was hoping for a code that could do something similar for locking multiple files... It may not be possible, but I thought I would check with the experts.
    Last edited by brolsen; 07-19-2014 at 02:46 PM.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro for Protecting Sheet (with same name) on multiple files at once?

    Why not add code set the protection to the code that creates the duplicate files?

  6. #6
    Registered User
    Join Date
    08-10-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Macro for Protecting Sheet (with same name) on multiple files at once?

    So here's the order:
    1. Duplication of files (needs to be unlocked so Step 2 can occur)
    2.) Multiple users enter data into form and save (Needs to be unlocked so they can type in data)
    3.) All the files are sent back to me (THIS IS WHEN I NEED TO LOCK THEM)

    I guess I could try to get the multiple users to protect them individually, but I was hoping to do something automatic so I don't have to "quality check" to make sure people do it on all 150 files.

+ 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. Macro to delete first working sheet in multiple files
    By Kaapoff in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-06-2014, 05:59 AM
  2. Macro to delimit and compile multiple files to one sheet:
    By llunde3 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-15-2013, 10:34 AM
  3. Macro involving multiple sheet and files
    By dguenther in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2011, 08:41 AM
  4. Macro for updating a sheet in multiple files
    By jijy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-26-2006, 01:21 PM
  5. [SOLVED] Protecting Multiple Sheet
    By Conditional Formatting in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2005, 05:51 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