Behavior of Locked Sheet Differs Based on How File is Opened

[TroelsL]

I've created a macro that enables double-clicking on a cell to show only rows with matching information. This allows me to quickly filter a large table with numerous filters.

The macro works as expected in the table, also after I've locked cells in other parts of the sheet.

However, I've noticed it only works on cells containing a dropdown list until I close the file. After I reopen the file, it works on all cells except those containing a dropdown. I have to unlock and relock the sheet with the same permissions, to make the function working again in all cells (including those with dropdown lists).

How can I ensure the file works properly upon opening, allowing me to use the double-click filter function in every cell, including those with dropdown lists?



For information:
I use this function to allow macros on the locked sheet

Please Login or Register  to view this content.

My macro double-click filter-function

Please Login or Register  to view this content.

[Greg M]

Hi there,

You don't include your workbook, so it's difficult to make meaningful suggestions

The first thing that occurs to me is that the routine "AllowMacroOnLockedSheet" should be called from a "Workbook_Open" routine - is this in fact the case?

Regards,

Greg M

[TroelsL]

Hi Greg

Yes, my workbook consist of this:

Please Login or Register  to view this content.

[TroelsL]

Hi there,

You don't include your workbook, so it's difficult to make meaningful suggestions

The first thing that occurs to me is that the routine "AllowMacroOnLockedSheet" should be called from a "Workbook_Open" routine - is this in fact the case?

Regards,

Greg M

Hi Greg

Can I offer additional information to you?

[Greg M]

Hi again,

Can I offer additional information to you?

You can indeed!

Regards,

Greg M

[TroelsL]

Hi again,

You can indeed!

Regards,

Greg M

Great! What information do you want? How can I best assist you?

Workbook:

Please Login or Register  to view this content.

Module1

Please Login or Register  to view this content.

Sheet1

Please Login or Register  to view this content.

How can I ensure the macro works properly upon opening, allowing me to use the double-click filter function in every cell, including those with dropdown lists?


*edited to correct an error.

[romperstomper]

Is the AllowMacroOnLockedSheet routine really in Sheet1?

[TroelsL]

Is the AllowMacroOnLockedSheet routine really in Sheet1?

Sorry, no. The AllowMacroOnLockedSheet routine is defined is in "Module1".

BeforeDoubleClick is in Sheet1