Re-opening .xlsm sheet post saving gives error

  • I have a macro-enabled sheet where I perform calculations by unprotecting the sheet and then protecting the sheet post calculations. The sheet has a button "CALCULATE" that triggers calculations.

    Scenario:

    1. I have performed a calculation and saved the sheet as is.

    2 Closed the worksheet and re-opened the same

    3. on Clicking "CALCULATE" now it gives error

    "Run-time error '1004':

    The cell or chart you're trying to change is on a protected sheet.

    To make a change, Unprotect the Sheet. You might be requested to enter a password.

    Kindly help

  • Without the code or the workbook, I think helping will be difficult. ;)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • 1. Enter the number of teams (say 4)

    2 Click generate


    This should throw an error and debug will take you to a particular line of code.


    Clicking generate again will throw another error and take you to another line of code


    Clicking generate for third time will produce the intended result.


    Click reset and click on yes. Now save the file and re-open and repeat the above steps to reproduce

  • Looks like it's because the sheet is protected. You can unprotect the sheet before the code runs


    Code
    1. ActiveSheet.UnProtect "your password"
    2. 'your code
    3. Activesheet.Protect "your password"

    Or useProtectWithUserInterFaceOnly

  • There are other errors in the code as well, e.g.

    Code
    1. LastRow = .Cells(.Rows.count, "A").End(xlUp).Row

    This will error because it is not within a With ... End With Statement.

  • Each subroutine and function has been written within unprotect and protect.


    The program works after the initial hickup. The question is why such an issue occurs after i close and open the file?


    This line of code is not used in the program.

    LastRow = .Cells(.Rows.count, "A").End(xlUp).Row

  • The first part of code to change to Upper case is working whereas the second part of changing the color is not. Kindly help