+ Reply to Thread
Results 1 to 5 of 5

VBA to Protect and Unprotect after refresh

  1. #1
    Registered User
    Join Date
    01-25-2021
    Location
    Atlanta, Georgia
    MS-Off Ver
    365
    Posts
    46

    VBA to Protect and Unprotect after refresh

    I have 2 basic queries on one sheet that refreshes with a button.

    I am looking for a VBA code that upon refresh the sheet Unprotects, Refreshes and Protects again. I keep getting an Error 9 code and not sure why........

    Thanks all!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,367

    Re: VBA to Protect and Unprotect after refresh

    You can protect or unprotect a sheet using either the actual sheet name you see in the GUI or the code name of the sheet you have to open the Visual Basic Editor to see. In my humble opinion the later is preferred as it allows the tab name to be changed but the code will still work so based on that I think this is what you're after:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    01-25-2021
    Location
    Atlanta, Georgia
    MS-Off Ver
    365
    Posts
    46

    Re: VBA to Protect and Unprotect after refresh

    I may not be doing something correct. I keep getting this error:
    Attached Images Attached Images

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,151

    Re: VBA to Protect and Unprotect after refresh

    Make sure that your data connections are not set up for background refresh. Otherwise you may be still refreshing data when trying to reprotect the sheet.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,367

    Re: VBA to Protect and Unprotect after refresh

    Also make sure the Refresh button is correctly assigned to the RefreshmyQuery macro (even just do it again to make sure).

+ 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. VBA unprotect sheet refresh OLEDB connection Protect sheet with exceptions
    By MarcoZanin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2020, 04:27 AM
  2. VBA to unprotect sheets x 2, refresh data then protect sheets
    By kaseyleigh in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 04-18-2016, 08:38 AM
  3. Macros - Unprotect/Refresh Pivot/Protect
    By luke.walker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2015, 12:27 PM
  4. Macro to unprotect refresh protect along multiple worksheets
    By kfryar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2014, 09:40 PM
  5. Replies: 1
    Last Post: 09-25-2012, 01:30 AM
  6. Protect and Unprotect using vba
    By mbrady1973 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-01-2009, 07:47 AM
  7. Protect UserInterface VS Protect/Unprotect
    By Desert Piranha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2006, 11:08 PM

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