Pop-Up Calendar Via VBA - Any Date Field

[kescco]

I am hoping someone can help on this. I have already created a calendar which can be used by clicking in a date field and then choosing Insert Date. I think a tutorial is on www.fontstuff.com.

What I am wondering is if there is a way for the calendar to pop-up any time a cell formatted as date is clicked on.

Thank You,

Kescco

 

[AlphaFrog]

• Right-Click on the sheet tab you want this to work on
• Select View Code from the pop-up menu
• Paste the code below in the VBA edit window.
• This assumes your Calendar macro is called OpenCalendar

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    If Target.Cells.Count = 1 Then
        str = Target.NumberFormat
        If ((InStr(str, "d") > 0) + (InStr(str, "m") > 0) + (InStr(str, "yy") > 0)) < -1 Then
            Call [I]OpenCalendar[/I]
        End If
    End If
End Sub

If the date format is Day-only or Month-only or Year-only, then this needs to be modified. It should work for any other Date formats (I think).

 

[kescco]

Thank you very much.

A little personal tweaking and it is running beautifully.

Kescco

 

[nkiouari]

The macro works great!
As I'm using a french version, I have changed the date language to french and it's so cool.
Thank you very much