Timeline slicer on protected sheet (Years, Quarters, Months, Days)

[jerem]

This is one of the most bizarre issue with Timeline slicers. It has existed ever since Timeline slicer exist (incl. Ms Excel 2013 and 2016). I have searched for solutions, none of which I found suitable. So here is mine:

Problem: You have a timeline slicer, when the sheet is protected, it works except for one feature: the scale selection on the top right hand corner (Years, Quarters, Months, Days). The most common solution available on the Internet is to create 4 slicers, each of them set to a different scale. Not very efficient nor aesthetic I think.

Solution: So here is my approach. It's not perfect but it suits me. Hope it can help others. The idea is to unprotect the sheet whenever the dropdown is hovered. No event will trigger when the timeline is hovered but an ActiveX has a MouseMove event that can be used. So the idea is to place an ActiveX label on top of the time scale drop down on the timeline, and have it unprotect the sheet for a short time when ever it is hovered by the mouse.

1. Draw an ActiveX label over the drop down list in the timeline
2. change its background to transparent and its caption to 0
3. Place the following code in the sheet code:

Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)    
    Label1.Visible = False
    Sheet1.Unprotect
    Delay (3)
    Sheet1.Protect
    Label1.Visible = True
End Sub


Private Sub Delay(lngSec As Long)
    Dim dteTime As Date
    dteTime = DateAdd("s", lngSec, Now())
    Do While Now() < dteTime
        DoEvents
    Loop
End Sub

Do not hesitate to comment and improve this.