+ Reply to Thread
Results 1 to 11 of 11

Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

  1. #1
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    I have a macro that runs a loop on most sheets to clear colored cells. All worksheets are protected and I can't seem to integrate my protect and unprotect macros (which work!) into the clearing macro. Here are all 3:


    Clear cells
    Please Login or Register  to view this content.

    Unprotect all sheets

    Please Login or Register  to view this content.

    Protect all sheets


    Please Login or Register  to view this content.

    How can I integrate the 1. unprotect all sheets, 2. run clear macro, and 3. protect all sheets?

    Note that "UserInterfaceOnly:=True" does not consistently work. It fails when the workbook is opened having been previously protected..

  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: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    Have you tried calling the subs from the sub that's clearing cells?
    Please Login or Register  to view this content.
    PS I changed the sub name to avoid conflict with the VBA method ClearContents.
    If posting code please use code tags, see here.

  3. #3
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    Norie, thanks for code it worked at my end...


    Please Login or Register  to view this content.
    Last edited by Parth007; 06-24-2015 at 10:42 AM.
    Regards
    Parth

    I appreciate your feedback. Hit * if u Like.
    Rules - http://www.excelforum.com/forum-rule...rum-rules.html

  4. #4
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    Thanks, that's simple! However, it seems to operate a bit clunky .. I'm not sure that the macros are working in the correct order. It asks to unprotect, then protect, then it seems as if it clears..?

  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: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    There's a small error in the code I posted, remove this line.
    Please Login or Register  to view this content.
    As for the code unprotecting, protecting and then clearing, I can't see that happening.

    The order of the code is call UnprotectAllSheets, clear, and then call ProtectAllSheets.

  6. #6
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    Norie,

    I adjusted your code but still, the process goes as follows:

    1. asks me to unprotect

    2. asks me to protect

    3. clears all blue cells

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

    Re: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    How exactly did you adjust the code?

  8. #8
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    Please Login or Register  to view this content.
    You'll notice that I added " Range("C3").Value = DateAdd("d", 7, Range("C3").Value)" but that doesn't seem to be the issue. I don't think.

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

    Re: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    I also notice you added this, but I'm not sure why.
    Please Login or Register  to view this content.
    Can you upload a sample workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  10. #10
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    I added that to save just prior to running the protection and clearing so that in case it was done in error we could exit without saving and reopen.
    Attached Files Attached Files
    Last edited by ksayet; 06-24-2015 at 04:58 PM.

  11. #11
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Compile 3 Macros into 1 (Unprotect > Run Loop > Protect)

    Have you had a chance to check out the attached workbook, Norie?

+ 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. [SOLVED] Unprotect > Run Loop > Protect
    By ksayet in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-24-2015, 09:35 AM
  2. Macros - Unprotect/Refresh Pivot/Protect
    By luke.walker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2015, 12:27 PM
  3. Macros to protect and unprotect sheets (Without a password)
    By David_S_Walker in forum Excel General
    Replies: 6
    Last Post: 03-30-2010, 06:44 AM
  4. Macros for Protect/Unprotect all sheets in a workbook
    By Paul Sheppard in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-16-2005, 10:36 AM
  5. Macros for Protect/Unprotect all sheets in a workbook
    By Paul Sheppard in forum Excel General
    Replies: 2
    Last Post: 08-04-2005, 11:30 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