locked
Remove Protection from a XLSM RRS feed

  • Question

  • I currently have a Macro enabled workbook that my company uses to help calculate and total information. That information is then taken and stored on hidden sheets via a macro that also protects it then a second workbook uses Macros to pull the data from it and into it.

    The issue we are running into is that the former workbook acts slow in Office 2013 when inputting data then tabbing to the next input zone (the sheets are set so that only certain fields can be modified) and it takes about 7-10 seconds before you can do anything. However, in 2010 it was fine. I did some digging and found that it is by design as Microsoft upped the security related to Macros and protected workbooks.

    My question is if anyone happens to know how to remove the protection? I have tried a few methods and changed the protection in one of the Macros then tried removing the protection Macros. Still no change. If I disable Macros from running at all in Excel, it is very fast, I can tab to the next field and input data immediately. That tells me that it is built into the Macro itself somewhere and I think I know where. It seems that every time it is moving to a new field it is having to run through the protection which is now SHA2 512bit vs the older SHA1 160bit. Each sheet has its own macro on top of the macro of the workbook, which I think is the culprit:

    Private Sub WorkSheet_Calculate()
    ActiveSheet.Unprotect Password:="XXXXXX"
    If ActiveSheet.Range("S42").Value = "N" Then
     ActiveSheet.Buttons("Button 1").Enabled = False
     ActiveSheet.Buttons("Button 1").Font.ColorIndex = 15
    Else
     ActiveSheet.Buttons("Button 1").Enabled = True
     ActiveSheet.Buttons("Button 1").Font.ColorIndex = 1
    End If
    ActiveSheet.Protect Password:="XXXXXX"
    End Sub

    If I remove the "Unprotect" part, it still acts slow but otherwise acts normal. However, if I remove the "Protect" part of this Macro it will cause the sheet to error out. I have tried quite a few things and had absolutely no luck.

    Anyone have any tips? We don't really need the protection but the calculations that the workbook does is very helpful time wise for our employees that use it.

    Thanks in advance.

    Tuesday, April 14, 2015 7:10 PM

Answers

  • Hi Jimmy,

    >>My question is if anyone happens to know how to remove the protection?<<

    Did 'Protection' mean remove the password from worksheet? If I understood correctly, the code you provided do unprotect the worksheet. We also can use Workbook.Unprotect methoed to remove protection from the workbook.

    For the perfomance issue, I would suggest that you add the code print the time begin and end for the every line of code to find which line of code cause this issue.

    And also here is an article about improving the performance of calculation for your reference:
    Excel 2010 Performance: Improving Calculation Performance

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Friday, April 17, 2015 3:39 AM

All replies

  • Hi,

    The burden is in the fact that it will calculate every time an action occurs. The macro enables and disables a button. So the real question would be what this button does. My advice would be to create a separate macro for your button. The check for the value is only done on one range.

    If the calculation is set to manual you might gain some speed. Question is does that satisfy the user.

    So in short, find an other sort of action that can trigger your macro.  

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    Tuesday, April 14, 2015 9:45 PM
  • There actually is a Macro for the button as well.

    The button itself actually completes the worksheet and takes everything and calculates it together into some hidden worksheets and then locks the workbook from being changed. After that is done, a second workbook has a Macro that calls to the first workbook and pulls data from it and places it in specific columns. It is vastly more complicated than it needs to be, it has Macros for Macros, but it is easier than trying to write a whole new workbook.

    There are 6 Macros themselves that are apart from each worksheets Macro:

    - Button
    - Unprotect
    - Protect
    - Complete
    - Email (the macro that sends a copy to a specified email on the workbook)
    - One I don't quite know what it does has this:

    Function PrevSheet(rCell As Range)
        Application.Volatile
        Dim i As Integer
        i = rCell.Cells(1).Parent.Index
        PrevSheet = Sheets(i - 1).Range(rCell.Address)
    End Function

    Then of course there is the above Macro which is in each sheet that acts as a day of the month.

    It is quite a badly done workbook but not much I can do except try every resource to find a patch type solution until we can build something better.

    Tuesday, April 14, 2015 10:58 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Wednesday, April 15, 2015 8:38 AM
  • Hi Jimmy,

    >>My question is if anyone happens to know how to remove the protection?<<

    Did 'Protection' mean remove the password from worksheet? If I understood correctly, the code you provided do unprotect the worksheet. We also can use Workbook.Unprotect methoed to remove protection from the workbook.

    For the perfomance issue, I would suggest that you add the code print the time begin and end for the every line of code to find which line of code cause this issue.

    And also here is an article about improving the performance of calculation for your reference:
    Excel 2010 Performance: Improving Calculation Performance

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Friday, April 17, 2015 3:39 AM