none
Design pattern for unprotecting and reprotecting sheets with VBA? RRS feed

  • General discussion

  • Many macros can't run when a worksheet is protected, so when I develop an Excel workbook that has protected worksheets, I have to unprotect worksheets when macros are called and then reprotect the worksheets once the macro has completed.

    The current design pattern I use for this is the following:

    I look at each procedure individually and consider which sheets need to be unprotected for it to run. Then in each procedure I write:

    Sub Test()
    Dim bSheet1Protected as boolean
    Dim bSheet2Protected as boolean
    bSheet1Protected = Sheet1.ProtectContents
    bSheet2Protected = Sheet1.ProtectContents
    bSheet1Protected.Unprotect
    bSheet2Protected.Unprotect
    
    '''The rest of my code
    
    if bSheet1Protected then Sheet1.Protect
    if bSheet2Protected then Sheet2.Protect
    End Sub

    But I imagine that maybe there are better ways of doing this. What is your preferred design pattern for unprotecting and reprotecting sheets with VBA?

    Monday, February 2, 2015 8:32 AM

All replies

  • Hi JP3O,

    I think it is a good practice to unprotect the worksheet before execute some code and protect it back after the code execute finished.

    >>But I imagine that maybe there are better ways of doing this. What is your preferred design pattern for unprotecting and reprotecting sheets with VBA?<<

    I am not able to find a better way since we always need to unproect the worksheet if some code didn't not executed successful when the worksheet is proected.

    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.
    Click HERE to participate the survey.

    Wednesday, February 4, 2015 2:01 AM
    Moderator