Behavior of Locked Sheet Differs Based on How File is Opened

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

Sub AllowMacroOnLockedSheet()
    Sheets("Sheet1").Protect Password:="Password", UserInterfaceOnly:=True
End Sub

My macro double-click filter-function

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim ClickColum As Integer
Dim ClickValue As String

ClickColum= ActiveCell.Column
ClickValue= ActiveCell.Value

On Error Resume Next

    If Application.Intersect(ActiveCell, [Top]) Is Nothing Then
        If ActiveCell.Value <> "" Then
            ActiveSheet.Range("A:az").AutoFilter Field:=ClickColum, Criteria1:=ClickValue
        End If
    End If
End Sub

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

Yes, my workbook consist of this:

Private Sub Workbook_Open()
    Call AllowMacroOnLockedSheet
End Sub

Originally Posted by 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

Hi Greg

Can I offer additional information to you?

Hi again,

Can I offer additional information to you?

You can indeed!

Regards,

Greg M

Originally Posted by rorya

Is the AllowMacroOnLockedSheet routine really in Sheet1?

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

BeforeDoubleClick is in Sheet1