Pop up macro calendar on cell change

[e561414]

Hello everyone.
I'll start with the credits; there's a really useful calendar code here:
VBA Express : Excel - Userform Calendar Control

Everything works fine, this part right here triggers it whenever I touch a cell with that format:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim DateFormats, DF
    DateFormats = Array("dd/mm/yy;@", "mmmm d yyyy")
    For Each DF In DateFormats
        If DF = Target.NumberFormat Then
            If CalendarFrm.HelpLabel.Caption <> "" Then
                CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
            Else: CalendarFrm.Height = 191
                CalendarFrm.Show
            End If
        End If
    Next
End Sub

But I actually want it to show up whenever the user tries to write something on the cell instead of showing up everytime I touch it. How can I do that without messing it up?

Thank you all in advance.

 

[VoG]

You need _Change not _SelectionChange.

 

[e561414]

You need _Change not _SelectionChange.

Hello Peter, I applied your suggestion and it triggers the macro only after I'm done entering the value in the cell, for instance, after hitting Enter, clicking outside or hitting Delete.

I just figured out that what I really need is to trigger the macro right when the user presses a key or double clicks.

Is this possible through VBA?

 

[VoG]

Try this with double clicks

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim DateFormats, DF
    Cancel = True
    DateFormats = Array("dd/mm/yy;@", "mmmm d yyyy")
    For Each DF In DateFormats
        If DF = Target.NumberFormat Then
            If CalendarFrm.HelpLabel.Caption <> "" Then
                CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
            Else: CalendarFrm.Height = 191
                CalendarFrm.Show
            End If
        End If
    Next
End Sub

 

[e561414]

Just checked it out and it works perfectly for the double click, Peter. Thanks a lot.

Have an amazing weekend.