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]

I haven't forgot about you @Rnkhch.

Before I start looking at the code again, I wanted to ask why you are choosing to have a 'merged' cell range? What is your goal of having the cells merged? You currently have shown the merged cells are 3 cells. What is in those 3 cells that you are merging?

I told you many posts ago that VBA code and Excel merged cells are 'rivals' and often requires 'referee' code to get them to coexist. So I guess I need to ask what are you merging specifically. Please provide an example or two of what is being merged.

 

[Rnkhch]

Hi Johnny,

Thank you That's a good question. The reason I merged three cells in M17:M36 (which is for special samples) was to create a wider cell area to be essentially equivalent in width to column E (which is for main samples). Now you might ask why I didn't just set the column width of M to be equal to E, and the reason is that I have implemented several dropdown menus above M17 (i.e. in the ranges of M3:O15) that control various properties of the application (such as dilution factor, assay standards, group definitions, pathogen strains, etc.) which are one column wide each and generally they belong together three-by-three in the range of M to O. This happens to be an unbelievably optimal and easy to follow setup from the scientific point of view, and the students love it. And the same goes for under the M17:M36 area. So widening column M would significantly affect the approachability, intuitiveness, and aesthetics of this "Setup" sheet. So M would become too wide compared to N and O, which would then need N and O to be widened as well to fit M, but most of the contents there are single- or two-digit numbers which don't need such wide range.

Of course, in the event that you don't succeed in applying your magic to the merged cells, I will have to reshape the sheet (because I don't want to leave it unprotected), but that would need quite a bit of redesigning all the dropdown menus and controls and figuring out another very intuitive interface.

So that's the long answer to your question , but I hope you can do something

 

[johnnyL]

See if this is a viable option for you.

Run the following code:

Sub UnmergeRangeThenCenterAcross()
'
    Range("M17:O36").UnMerge
    Range("M17:O36").HorizontalAlignment = xlCenterAcrossSelection
End Sub

That code will unmerge M17:O36 & then change that range to 'CenteredAcross'.

Afterwards, when you put an entry into the M column in the range of M17:M36, the entry will be centered across the columns M thru O.

Try it out & let me know.

 

[Rnkhch]

Actually not a bad idea. And the pasting code works well too.

Is it possible to make N17:O36 unselectable? so that people cannot even click on them? Then they will only be able to click on M17:M36.

Thank you!

 

[johnnyL]

Do you have any locked or hidden cells on the password protected sheet?

 

[Rnkhch]

Everything is locked and hidden except the monitored ranges that people can edit.

I just tried unchecking the 'select locked cells' during protecting, but it makes the sheet a bit weird in that you can't click in most areas and if you use the arrow keys to navigate, then the active cell jumps across ranges. So I was hoping that I could leave the 'select locked cells' option checked, but specifically make N17:O36 unselectable through VBA if possible.

 

[Rnkhch]

Something like this lol :

Private Sub UnselectableRanges()

            ActiveSheet.Range("N17:O36").EnableSelection = False

End Sub

I just cooked it up lol, but the syntax is probably messed and of course it didn't do anything

 

[johnnyL]

Unprotect the sheet and then run the following code:

Sub LockRangesExceptMonitored()
'
' Run this on unprotected sheet aka not passworded
'
    Dim UnlockedRange1  As Range
    Dim UnlockedRange2  As Range
    Dim SheetPassword   As String
'
    SheetPassword = "123456"                                                        ' <--- Set this to your desired password
    Set UnlockedRange1 = Range("E3:G39")                                            ' <--- Set this to the range of cells to keep unlocked
    Set UnlockedRange2 = Range("L17:M36")                                           ' <--- Set this to the range of cells to keep unlocked
'
    Cells.Locked = True                                                             ' Lock all cells
    Cells.FormulaHidden = True
'
    UnlockedRange1.Locked = False                                                   ' Range to keep unlocked
    UnlockedRange1.FormulaHidden = False
'
    UnlockedRange2.Locked = False                                                   ' Range to keep unlocked
    UnlockedRange2.FormulaHidden = False
'
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells                                   ' Enable clicking of unlocked cells
    ActiveSheet.Protect SheetPassword                                               ' Protect sheet with password
End Sub

Let me know if that works for you.

 

[johnnyL]

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

You will have to protect the sheet with a password after you run the code. Just make sure the 'Select locked cells' box is NOT checked

 

[Rnkhch]

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.


I just looked up XlEnableSelection here: XlEnableSelection enumeration (Excel)

Too bad that there is no option for xlLockedCells

So it seems that there's no way to only make N17:O36 unselectable?