+ Reply to Thread
Results 1 to 14 of 14

Unable to run code in between "Unprotect" and "Protect" object.

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    12

    Unable to run code in between "Unprotect" and "Protect" object.

    Hi,

    I am trying to select a report filter in between the "Unprotect" and "Protect" object. The issue is the report filter won't stay visible long enough to make a selection before it closes and protects again. I have tried to do the wait command in order to prolong the time that the report filter stays open, but that doesn't work either because it won't allow you do make a selection during the wait.

    The code will unprotect and run if I leave off the, "Protect Password:="Secret". But that will just leave everything open for edit which is what I don't won't. I understand that the object can still be edited or modified during the open window in between "Unprotect" and "Protect" keywords. But this is the only method that I've had any measure of success with. Any/All suggestions are welcome.


    The code I am using for this is pretty simple...something like:

    Please Login or Register  to view this content.

  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: Unable to run code in between "Unprotect" and "Protect" object.

    Which worksheet are you actually protecting/unprotecting and running the code on?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    Hi,

    Its actually "Sheet1". But I haven't needed to specify the sheet name in this part of the code in order to actually protect/unprotect. I have even tried the "UserInterfaceOnly:=True" option but to no avail.

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

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    You should always specify the worksheet and avoid using ActiveSheet if possible.

  5. #5
    Registered User
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    Thanks for the response,

    So I have specified the worksheet before and it didn't help the issue that I am asking about. And I use "ActiveSheet" because it is the only way I know how to have a user dynamically choose chart functionality through a combo box and vba code. I am looking for help or sample code to help with my original issue that I posted, which is, I can't perform the report filter functionality in between unprotected and protect. This same type of code is successful with choosing trend lines and data labels on the chart object but not report filtering.

    If anyone could help me with my original issue that would be great -

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

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    Can you post the rest of the code and/or a sample workbook?

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    What are you actually unprotecting/protecting?

    This looks like you are calling 2 functions/subs called Unprotect and Protect.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    Here is an example of the code:

    Please Login or Register  to view this content.
    I am sure there is a better way to do this, but my limited experience constrains me. Basically, the user chooses functionality like add trend lines, report filters, adding data labels. All of the other code is similar to the code above.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    I don't think the code is actually unprotecting/protecting anything - see my previous post.

  10. #10
    Registered User
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    Thanks again,

    But no that's not correct - I already mentioned before that it is unprotecting/protecting. I have ran the code several times and it is indeed unprotecting and protecting the current worksheet. So again could someone offer maybe some better coding examples of how to unprotect - run report filter on a chart -then reprotect?

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

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    When I put these 2 lines of code in a sub and try to run it I get "Compile error: Sub or Function not defined".

    Please Login or Register  to view this content.
    The only way I can avoid that error is by defining subs/functions called Unprotect and Protect which take one parameter, Password.

    For example:
    Please Login or Register  to view this content.
    So how those lines of code are unprotecting/protecting anything in your code is kind of a mystery to me.

    PS If I add worksheet references, for example,
    Please Login or Register  to view this content.
    the code works fine.

  12. #12
    Registered User
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    Thanks I am trying that -
    Last edited by eric1234; 08-28-2013 at 12:43 PM.

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

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    The code you've just posted should, in theory, work.

    The only problems I see with it are the use of ActiveSheet, ActiveChart etc.

    Is there any other code?


    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

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

    View Pic

  14. #14
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Unable to run code in between "Unprotect" and "Protect" object.

    Is your code in a sheet module? If so, use Me to represent the sheet object to avoid this confusion in the future:
    Please Login or Register  to view this content.
    Norie is right, you should always declare a sheet to make code readable. If the code was in a standard module, it will fail as Norie has already explained.

    As for your question, give this a try:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ 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] Activate "sorting" feature when worksheets are setup to "password protect" itself
    By nenadmail in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2012, 09:04 PM
  2. "The "sheets" method from the "_Global" object have failed."
    By mankit87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2011, 08:53 AM
  3. Error msgs: "Object varible or with block variable not set"; "subscript out of range"
    By menyanthe in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-26-2009, 04:58 PM
  4. [SOLVED] What is Error "Method "Paste" of object "_Worksheet" failed?
    By vat in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-17-2006, 04:10 PM
  5. [SOLVED] Problem: Worksheets("New Style 2006").Unprotect Password:="naPrint"
    By Karoo News in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2006, 10:45 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