"The object invoked has disconnected from its clients"

I have a "Clear All" button that runs a macro that clears the contents of various cells, resets some check boxes and does some other resetting on various sheets within a spreadsheet.

There are a few cells in this spreadsheet which by default contain formulas (which fetch data from other sheets if data is entered on those sheets), but the user also has the option of directly data in those cells (using a pulldown), thus overwriting the formulas.

Part of the "Clear All" button will clears the contents of these cells, and then puts the original formulas back in. However, these cells also contain conditional formatting (they turn red if the cell to the left has a figure entered into it, but the current cell is blank), and they also contain data validation. I have found in the past that it is not possible to enter a formula in a cell while it has data validation, because the formula does not match anything from the data validation list. Therefore, the macro does this (it's not pretty, but it works):

1 - clears the contents of the cell (any data that might have been entered, be it directly typed, via the pulldown, or via the formula)
2 - clears the data validation from the cell (to allow the original formula to be put back in)
3 - puts the original formula back in
4 - puts the data validation back in

The odd bit is, when the sheet in question in unprotected, the macro runs fine. But when the sheet in question is protected (bearing in mind that the cell itself is unprotected), the macro crashes, with the error message:

"The object invoked has disconnected from its clients", with this highlighted:

Please Login or Register  to view this content.

Is there a way I can get this macro to work without having to just leave the sheet unprotected?

That I could. Would you happen to know the command and syntax for protecting and unprotecting sheets within a macro?

Never mind, I found the command online, and it works a treat. Thanks!