Why VBA macro doesn't work on certain protected sheets?

[Rnkhch]

Hello,

I'm encountering this strange problem that my macro does not work on only one of my protected sheets, but it works perfectly on other protected sheets. I only check the first two boxes in the protect dialog box (i.e. 'select locked cells' and 'select unlocked cells'). All other boxes are unchecked. And I apply a password.

This is specifically related to my post here and the "Export" macro: Is it possible to implement an export/import feature for the unlocked cells in my Excel file? (Through VBA?)

After several tries, I believe I was randomly able to get the macro to work on all protected sheets in my smaller workbook. All I did was to delete all macros and rewrite everything. I pasted them into my Visual Studio Code, and then I repasted them back into my file after saving, closing, and reopening it. So I got excited and I thought I this should work on my other similar files.

Then I applied the macro to my larger file which is identical to the smaller one, but has larger capacity (for more samples). And now no matter what I do, I cannot get the Export macro to work on the "Plates-Input" sheet, which has the bulk of the data (~55k unlocked/unhidden cells) in this file. Tha's why I made this post. (So if I leave this sheet unprotected, my output CSV file is huge (1.6MB). If I leave this sheet protected, Export only extracts the unlocked cells from the other two sheets and the resulting CSV file in 24.1KB )

I tried several tricks that found online, including trying to unprotect the Plates-Input sheet at the beginning of the Export macro and then reprotecting it at the end. But nothing worked, although they may have not worked for wrong syntax or location (line) as I'm not very VBA-literate

How can I figure out what's different between my three protected sheets? I mean what "relevant" settings can possibly be different?

I don't know what's happening, but it appears to me that Excel remembers "things" in "some" location? And whatever/wherever this is, needs to be cleared/reset somehow?

Any input is highly appreciated.

 

[Alex Blakenburg]

Tha's why I made this post. (So if I leave this sheet unprotected, my output CSV file is huge (1.6MB). If I leave this sheet protected, Export only extracts the unlocked cells from the other two sheets and the resulting CSV file in 24.1KB

This sounds like a different problem to me.
Go to the "Plates-Input" sheet.
Ctrl+F and search for Format > Protection > Locked unchecked.
When you get the search results click in the bottom pane and hit Ctrl+A.
This should select all the found cells.

Alt+F11 - switch to the VBA editor.
In the Immediate window (Ctrl+G) copy the below including the question mark and tell me what number it comes back with.

?selection.count

 

[Rnkhch]

This was an excellent suggestion. I proceeded as you mentioned, and Ctrl+F found 52813 cells. Upon selecting all, Excel became unresponsive which makes sense given the huge number of cells. I left the computer to use the restroom, and upon returning, I saw the recover files dialog to the left of the Excel window, so I assume the file crashed and reopened.

Then I tested the Export, and now it worked beautifully So I assume that the crash must have triggered something in the file?

I tried to redo the process of Ctrl+F, and one time it crashed again, and one time the find window closed on its own. So I'm unable to proceed to the later stages of your directions to tell you the number that you were looking for (The file is huge with 249,820 cells with data and 229,620 formulas )

But since everything is working now, I'm content, and I'll make all future updates of my applications with the current working files hoping to maintain the working status.

So unless you want to follow up on this further and have other suggestions on how to proceed (which would be great in order to prevent similar issues in future), I'll consider this done for now. Hopefully if you get a chance, you can implement those enhancements on the other thread plus one more, which I'm gonna mention there.

Thank you so much

 

[Alex Blakenburg]

I am not sure I can do much more without having access to the file so if you are ok for now perhaps we will leave it there for now.
I am little curious though, unlocked cells normally refer to user provided data, in which case 52k+ seems an awfully large number. It is also inconsistent with have 229k formulas and 249k total cells which would imply there are only 20k cells that should be user provided.
Note: since there is no way of counting unlocked cells except by looping through all the cells, so I have sized the array to a 100k. Just keep that in mind in case you think to amend the spreadsheet in future so that it has more than 100k of unlocked cells.

 

[Rnkhch]

Thank you. I guess we can leave it there for now.

So, there are 6 sheets in the workbook, only 3 of which are covered by the import/export functions, and the Plates-Input sheet, that has the bulk of user input cells, has only 23,980 formulas. The data in this sheet correspond to 525 96-well plates (50,400 cells) that scientists use to do experiments with, so that's the bulk of the 52K data from this sheet. Of course, generally not every single well of those plates are used in every experiment, so even after completion of the experiment over a long time, at most ~20% of the cells will have been used.

The bulk of the formulas, i.e 168,655, are in the Plates-Data sheet which is not covered by the import/export functions.

And thanks for setting the number at 100K. I think in the largest version of the file which I will make in near future, the Plates-Input sheet will have about 75K unlocked cells corresponding mostly to 750 96-well plates (72K cells). I'm gonna make that file from scratch so as to avoid the problems that we experienced with the two smaller files before.

I also have several thousands of defined names that are either LAMBDA functions for complex statistical calculations or dynamic ranges that specify the ranges for several charts in the Charts sheet

I think if you see the workbook, you'll be amazed I'm even telling my boss to patent it or something like that so we can license it to scientists. There is no software on the planet that can do what this application can do.

Thanks again for all your help