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!

 

[johnnyL]

Hmm. So this works, but is basically the same as protecting the sheet and leaving the locked cells unchecked without the code. So I can basically protect as usual and set the password in the dialog box.

Not sure what you are saying there. Can you rephrase? What is not working like you wanted?

 

[Fluff]

It looks like the setting of the password in the last step is not taking.

Pretty sure that line needs to go before

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 

[johnnyL]

Thank you fluff!

I changed the end of the code to:

    ActiveSheet.Protect SheetPassword                                               ' Protect sheet with password
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells                                   ' Enable clicking of unlocked cells
End Sub

The password stuck like glue.

 

[Rnkhch]

What I meant is that the two ways appear to be the same:
1) using your code and leaving locked cells unchecked (with the password in the code)
2) Not using your code and leaving the locked cells unchecked and setting the password in the protect cells dialog box

Basically what I was saying at the second paragraph of my post #16, that it would be nice if everything was clickable (except N17:O36). It feels a bit weird to not be able to click at most of the cells, and of course the jumping around when you use he arrow keys to navigate sideways.

 

[johnnyL]

I'm confused. I thought you said that you wanted all of the cells locked (can't be clicked on) except for the monitored ranges.

Is that not what you want? Please provide more detail of what you want to be able to do that you can't do after running the 'locking' code that I posted.

 

[Rnkhch]

So basically ideally I want a scenario which would be behave like this:

1) Sheet protected with a password (which provides protection)
2) not being able to click on N17:O36 (which makes only M17:M36 selectable in that area for 'special samples' which would make data entry there easier so users don't accidentally click on N17:O36)
3) Being able to click anywhere else (locked or unlocked) (which avoids that weird feeling that I was telling above)

 

[johnnyL]

It sounds like you are leaving out some info.

You want every cell clickable except N17:O36. What are you protecting on the sheet?

 

[Rnkhch]

Oh sorry, I should have mentioned that there are other cells that I haven't talked about/shown (for simplicity) in those files that I attached (in this and the other thread) that contain formulas. For example, column D has formulas that automatically generate sample numbers upon entering sample names in column E. Also the whole range of D3:G42 has a few conditional formattings that for example highlight the rows if you accidentally enter duplicate names, etc. So the sheet is actually much more involved/advanced than I've presented so far Actually my colleagues say that it's a statistical/analytical "marvel" lol (And of course I can describe what the other sheets do if you're interested, which involve sample data entry as well as a bunch of statistical calculations etc.)

And so that's why I would ideally want to just protect the sheet with a password and leave only the two 'select locked and unlocked cells' checked so people can only enter data in the monitored ranges. And as an added bonus, I was hoping that I could only make O17:N36 unselectable which would be super cool.

P.S.
The pasting code you developed in the other thread has made my application even more "marvelous"

 

[johnnyL]

I'm still confused, so let me see if I can come up with a different way to handle N17:O36.

 

[Rnkhch]

Sure, thank you so much. And definitely ask any questions about anything that you want more clarification on