How to temporarily unprotect a sheet to allow VBA code to run and reprotect immediately, using VBA?

[Rnkhch]

Hello,

First a little background about this question. Actually this is related to and in continuation of another thread of mine (Is there any way that 'filling' can be restricted to values only and not formatting, perhaps through VBA?). So in this thread, johnnyL wrote magical code (particularly in post #95 which is marked as solution) that enabled all filling/paste/paste special/undo operations to be done without any of the formatting affected at all, and the code works beautifully in unprotected sheets. There is just a tiny issue that occurs in protected sheets and only with merged cells, and we decided to make another thread here for it to be more specific.

So basically, when I paste anything in the merged cells (i.e. M17:M36 in the other thread), I get runtime error 1004, application-defined or object-defined error which shoots to the following highlighted code:

If .MergeCells Then .UnMerge '

with the word "Unmrged" highlighted.

I have attached here a simplified version of my analysis template with johnnyL's latest code already implemented in it. I have locked/hidden all cells except the editable cells, and I have protected the sheet with the password "123456" (Analysis-template).

Of course, one possibility is to leave the sheet unlocked, but I'm worried that the students will start to mess up with the text, formulas, formatting, etc., which won't be good. But I thought of another idea, which is to momentarily unprotect the sheet and allow the particular portion of the code for merged cells to do its magic, and then immediately protect the sheet back. If the VBA experts here think this is possible, I would highly appreciate any input. Of course, if any other even better strategies (than what I thought) are possible, please share as well.

Thanks much!

 

[breynolds0431]

Hi.

You can unprotect and reapply the protection settings very easily. The protection settings can be customized as shown below.

Just wrap whatever part of your code needs to make a change to the worksheet with the .unprotect and .protect code.

ActiveSheet.Unprotect "123456"
If .MergeCells Then .UnMerge
ActiveSheet.Protect "123456", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True

 

[johnnyL]

So are you saying that all you do to get the code to write to the merged cells is unprotect the sheet, write to the merged cells, protect the sheet?

 

[Rnkhch]

So are you saying that all you do to get the code to write to the merged cells is unprotect the sheet, write to the merged cells, protect the sheet?

(not sure if your question was for me; if it was, it's above my brain lol so whatever you think is the best, but I guess you were likely asking breynolds0431)

 

[johnnyL]

I was asking you @Rnkhch.

I am asking, exactly what steps you take to unprotect the sheet, paste to a merged cell, protect the sheet again.

 

[breynolds0431]

So are you saying that all you do to get the code to write to the merged cells is unprotect the sheet, write to the merged cells, protect the sheet?

If that's where the sheet protection issue is coming into play, then that should do it.

You could also put the .unprotect prior to the first With and the .protect after it's End With

 

[Rnkhch]

Oh I see. Well, I thought it would be logical to unprotect the sheet with the supplied password, and let your code run, and then protect it back with the same password.

Oh and perhaps with a conditional statement specifying that this process should occur only if pasting is targeted to merged cells. If not, then the regular code can run.

 

[Rnkhch]

By the way, I just did some detective work regarding the properties that breynolds0431 mentioned. Of course, not through VBA code, but through the protect dialog box.

I noticed that even if ticked all the check marks in the protect dialog box that appear to correspond to the properties that breynolds0431 mentioned, the error/bug still happens. So it is something else that protection does that is blocking the code execution.

So I believe the only way the VBA code can run is through what I proposed which is to unprotect, run the code, and then reprotect. Just thought to share this observation with you guys.

 

[johnnyL]

@Rnkhch I can only see two paths for you from the information as I understand it:
1) Leave the merged cell range unprotected
2) Unprotect the sheet for every write, then protect the sheet afterwards, which kind of defeats the purpose of protecting the sheet.

I don't see how you can intercept the flow of excel that is producing the error that you mentioned.

 

[Rnkhch]

Hi,

Just making sure I understood you correctly:

1) Leave the merged cell range unprotected

Well, the merged cells range, i.e. M17:M36, is "unlocked"; it has always been unlocked as the rest of the "monitored ranges" have been unlocked. So even with the sheet "protected", the entire monitored ranges are "unlocked" (and "unhidden") which I did through the "protection" tab of the "format cells" dialog.

2) Unprotect the sheet for every write, then protect the sheet afterwards, which kind of defeats the purpose of protecting the sheet.

I meant a momentary unprotecting just to allow "paste" to happen, and then reprotect. Since this would happen in a split second, I don't think it would cause any safety risk.


There is "something" about protecting a sheet (and I haven't been able to figure out what it is) that blocks the merge/unmerge section of your code from working. I was hoping that the cause relates to one of the protect options that can be checked/unchecked during protecting, but I found that none of those options are the cause, which is what I was saying in my post #8.