+ Reply to Thread
Results 1 to 9 of 9

Unlock & Lock Sheets Macro

  1. 01-24-2017, 09:58 AM #1
    eduardbirkaya
    eduardbirkaya is offline
    Registered User
    Join Date
    12-21-2016
    Location
    Minnesota, USA
    MS-Off Ver
    2010
    Posts
    11

    Unlock & Lock Sheets Macro

    Hello all,

    I am trying to find a solution to my workbook, I have a macro that moves entire rows between tabs that I created for my work place (with your guy's help). However at current state the macro will not work if the given sheet (tab) is locked, I am looking for a work around by either ignoring the locked tabs (ideally) or unlocking and then locking the individual tab post the movement.

    Here is the code I am using:

    Please Login or Register  to view this content.
    Any suggestions? Thank you for your time! :-)
    Reply With QuoteRegister To Reply
  2. 01-24-2017, 10:18 AM #2
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Unlock & Lock Sheets Macro

    This would do it I think... but it does assume that the sheet is protected without password, you will have to add the password in quotes behind the .unprotect and .protect lines if you need to specify one.

    Please Login or Register  to view this content.
    If you want to ignore locked tabs (meaning skip the code for any locked sheet), then change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    and remove
    Please Login or Register  to view this content.
    from the end
    Last edited by Arkadi; 01-24-2017 at 10:20 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.
    Reply With QuoteRegister To Reply
  3. 01-24-2017, 10:38 AM #3
    eduardbirkaya
    eduardbirkaya is offline
    Registered User
    Join Date
    12-21-2016
    Location
    Minnesota, USA
    MS-Off Ver
    2010
    Posts
    11

    Re: Unlock & Lock Sheets Macro

    Hello Arkadi,

    In what format would I type the password behind the .unprotect and .protect (I am using "temp" as the current password). Sorry I am a noob when it comes to this.

    Thank you!
    Reply With QuoteRegister To Reply
  4. 01-24-2017, 10:55 AM #4
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Unlock & Lock Sheets Macro

    just in quotes behind the command:

    Please Login or Register  to view this content.
    and 
    Please Login or Register  to view this content.
    or you can use the actual name of the parameter but don't have to:

    Please Login or Register  to view this content.
    and 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 01-24-2017, 11:21 AM #5
    eduardbirkaya
    eduardbirkaya is offline
    Registered User
    Join Date
    12-21-2016
    Location
    Minnesota, USA
    MS-Off Ver
    2010
    Posts
    11

    Re: Unlock & Lock Sheets Macro

    When I am using this, I am getting the following error:

    Run-time error '1004'

    The cell or chart that you are trying to change is protected and therefore read-only

    To modify a protected cell or chart, first remove protection using the unprotected sheet command (review tab, changes group). You may be prompted for a password.
    I used the following code:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 01-24-2017, 11:26 AM #6
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Unlock & Lock Sheets Macro

    wow I'm stupid... just realized the sheets that are protected are others, not necessarily the one being changed.... let me get back to you in a few.
    Reply With QuoteRegister To Reply
  7. 01-24-2017, 11:48 AM #7
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Unlock & Lock Sheets Macro

    originally I took care of the scenario where the source sheet was locked, and the row deletion would be stopped by the protection, but it did not account for each individual other sheet (destinations) to be protected. Give this a try (assumes all sheets have temp as the password, change those as needed.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 01-24-2017, 12:07 PM #8
    eduardbirkaya
    eduardbirkaya is offline
    Registered User
    Join Date
    12-21-2016
    Location
    Minnesota, USA
    MS-Off Ver
    2010
    Posts
    11

    Re: Unlock & Lock Sheets Macro

    Thank you very much Arkadi! this worked like a charm :-)
    Reply With QuoteRegister To Reply
  9. 01-24-2017, 12:17 PM #9
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Unlock & Lock Sheets Macro

    Glad to hear it
    Thanks for marking as solved, and for the rep!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] unlock sheets apply macro then lock sheets again
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2015, 10:21 PM
  2. Lock and unlock sheets with password , diffrent usernames different actions
    By Barry Engelbrecht in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-20-2013, 07:06 AM
  3. Macro to lock/unlock computer
    By wfm007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2013, 02:18 PM
  4. VBA to unlock cells, run macro then re-lock them
    By tiger01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 02:24 AM
  5. Lock and Unlock sheets in a shared workbook?
    By Dulanic in forum Excel General
    Replies: 0
    Last Post: 02-15-2011, 01:24 PM
  6. Lock and Unlock Sheets based on Cell values
    By Mujahid_Sgd in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-02-2011, 05:18 AM
  7. using VB code to unlock and lock different areas on multiple sheets
    By Healthwatch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2010, 11:12 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1