+ Reply to Thread
Results 1 to 8 of 8

Macro: copy to new sheet that is locked or password protected

  1. #1
    Registered User
    Join Date
    11-15-2016
    Location
    Albuquerque, NM
    MS-Off Ver
    2016
    Posts
    19

    Macro: copy to new sheet that is locked or password protected

    I am working on a macro to automate an equipment charge-out form. We are a small environmental firm who uses equipment in environmental clean-up, for which we charge our clients. This is meant to make an invoice for them out of our master equipment list, to prevent wasting paper and sharing rates unnecessarily. It should only copy the rows related to the equipment that project used.

    So far:
    There is a button to run the macro, which reads sheet1 (named 'DO NOT PRINT') and checks column E. If there is any value in column E, then it copies that entire row to sheet2 (named "PRINT FORM').

    It copies values, formatting, and the password protection we have on all unshaded cells.

    What we are looking for:
    A change that would make sheet2 fully protected, so no cell can be edited after it is copied there, or a 'print only' version (for legal purposes in environmental clean-up jobs).

    Is this possible? The current password in the workbook is INTEP

    This is the great code submitted to us by AlphaFrog:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro: copy to new sheet that is locked or password protected

    s2 is already protected. As long as you make sure all cells are LOCKED before protecting the sheet, no cell can be edited.

    Then this small change will make sure the cells you paste INTO are not changed:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-15-2016
    Location
    Albuquerque, NM
    MS-Off Ver
    2016
    Posts
    19

    Re: Macro: copy to new sheet that is locked or password protected

    Hmm. Maybe I am not using it correctly.. I altered the overall code to say this, inserting the suggested code where I thought it might go:
    Please Login or Register  to view this content.
    When I run it, I can still edit cells in sheet2.
    We are trying to make it work so that no cell in sheet2 can be edited once copied there.
    Do you think I need to place this new piece differently? Or do I need something different?
    Thanks for your help so far,

    j

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro: copy to new sheet that is locked or password protected

    Quote Originally Posted by jstakutis View Post
    When I run it, I can still edit cells in sheet2.
    We are trying to make it work so that no cell in sheet2 can be edited once copied there.
    I think you may have missed my initial reminder:
    s2 is already protected. As long as you make sure all cells are LOCKED before protecting the sheet, no cell can be edited.

  5. #5
    Registered User
    Join Date
    11-15-2016
    Location
    Albuquerque, NM
    MS-Off Ver
    2016
    Posts
    19

    Re: Macro: copy to new sheet that is locked or password protected

    Thank you. Okay. I understand that there is a solution, but I am not sure how to apply it.

    The way a person would use sheet1 would be one of our geologists or hydrologists would enter quantity and dates used for a certain piece of equipment. Sheet1 has is protected so that only shaded cells are able to be edited. I think I am misunderstanding some difference between locked and password protected. If I enter the password, I can edit unshaded cells, but other employees would not have that password.

    What we are trying to do is have a workbook set up in which a person can enter things into sheet1, only the shaded cells.
    When he or she clicks the macro button, the rows they actually used will copy to sheet2 but not be editable after that.

    So, the part I don't quite follow, is how I can make sure all cells are "locked before protecting the sheet"?

    Could the code sequence be something like this?
    (Considering that the sheet is password protected when an employee logs in and can only edit shaded cells):
    1. Employee enters quantitites and other needed information into shaded cells.
    2. Employee clicks button

    The macro then
    1. makes sure all cells are locked, (is this the part I am missing?)
    2. checks column E of sheet 1
    3. any rows with values are copied to sheet2
    Then the employee can print sheet2, but not edit any cells in sheet2?

    Any clarification would be appreciated.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro: copy to new sheet that is locked or password protected

    "Locked" is a characteristic of each individual cell, in my experience most cells on new workbooks are in a "locked" status. What this means is that IF YOU TURN ON PROTECTION for a sheet, all the cells marked as "locked" previously would not be uneditable. If you set the status of any cell to "unlocked", then when the sheet is later PROTECTED, those cells would still be editable. This is what was done on your Sheet1 by someone.

    To check the status of cells that you're still able to edit after sheet2 is protected:

    1) unprotect the sheet
    2) select the problem cell(s)
    3) Press Ctrl+1 to open the "Format Cells" window
    4) Go to the PROTECTION tab and make sure [x] Locked is set.

  7. #7
    Registered User
    Join Date
    11-15-2016
    Location
    Albuquerque, NM
    MS-Off Ver
    2016
    Posts
    19

    Re: Macro: copy to new sheet that is locked or password protected

    I finally followed everything in the correct sequence to get the results I was describing! Thank you so much. And especially thank you for explaining it more than one way, since I was not following.

    I think the first time I tried it, I was confused because it copied values and not formatting, which looks a bit ugly. (it was a very busy time with other projects, now I have the time to focus on this again).

    However, your suggestion to lock all cells in sheet 2 and then substitute this piece:
    Please Login or Register  to view this content.
    This did copy the indicated rows into a fully uneditable sheet2, which is the important part.

    I do still need to copy values AND formatting. If I cannot figure that out soon, i will likely post it again and see what the forum thinks!

    The updated sheet with the full macro is attached.
    Attached Files Attached Files

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro: copy to new sheet that is locked or password protected

    Maybe try the different PasteSpecial options.

    xlPasteValues
    xlPasteValuesAndNumberFormats
    xlPasteAll

+ 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. Need Macro + Password Protected Sheet
    By Casper99 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2016, 03:58 PM
  2. Replies: 1
    Last Post: 03-12-2015, 03:55 PM
  3. [SOLVED] Adding a macro that will work even when the sheet or workbook is protected (locked)
    By Predictedizard in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-08-2013, 01:01 PM
  4. How to run a macro in password protected sheet
    By leo73pk in forum Excel General
    Replies: 15
    Last Post: 06-14-2013, 05:33 AM
  5. My password-protected vba code is locked for viewing...
    By Stu M in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-25-2009, 04:51 AM
  6. copy password protected sheet
    By daviddoria in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-31-2008, 06:53 AM
  7. PLEASE HELP! Copy sheet with password protected cells debug error
    By bsnapool in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-10-2006, 07:04 AM

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