VBA Calendar Control without Control

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • PM me your email address & i will send you the file.

    Unfortunately the attachments functionality was broken by the site hosts & they have yet to fix it. Since then all previously attached files have disappeared :yikes:


    (Luckily I have a copy of some of the better ones, such as Phil's calendar) :)

  • Hi Phil:

    I have been using your calendar and it works great. However, one can only get a date from this current year and forwards. That is, if today is Jan 13, 2004 than the calendar displays only 2004 and onwards. If I need to paick a date in 2003 the claendar does not allow that. How can I modify your code to display previous year and months? I am a novice at understanding the code, so, any help will be appreciated.
    Thanks Gops

  • I have updated this Calendar considerably to include:

    1. Some event handlers that call up the calendar when any cell that is formatted as a date are selected.
    2. Use of the calendar date picker to select a date to be inserted into a user form.
    3. Show years past and future.

    I also have another version that uses a class module instead of redundant lines of code for each button.

    If anyone wants either version, email me and I'll gladly forward as freeware.

    When the attachments work again I’ll post the new versions.


  • Thanks to you for seeing it through. I have used it quite a bit and I appreciate your work on it. Thanks for sending me the updates too! I am very grateful for persons like you and the rest of the OzGridders who are so willing to share their work! :biggrin:


  • I have been playing around with the file Phil sent me called Calendar8 which has a routine in the Worksheet_Change event to see if the Target is formatted as a date. The code will correctly launch the calendar if the selected cell is formatted to the format referenced in the code (see below)

    If Target.NumberFormat = "mm/dd/yy" Then
    If CalendarFrm.HelpLabel.Caption <> "" Then CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height Else CalendarFrm.Height = 191
    End If

    My question is this: Could this be amended to recognize ANY valid date format? My attempts have not yielded the desired result.

    I am sure that I am looking at the forest and wondering where all the trees are. If someone could help me remove the hand from over my eyes I would appreciate it. :biggrin:

  • I am using Phils' calendar with the following routine and it works.

    If Target.NumberFormat = "yyyy-mmm-dd" Then
    If Target.Address = "$F$3:$G$3" Then
    If ActiveSheet.Range("$C$68").Value &gt; 0 Then

    end if
    end if
    end if

  • originally posted by Brandtrock


    If Target.NumberFormat = "mm/dd/yy" Then
    If CalendarFrm.HelpLabel.Caption <> "" Then CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height Else CalendarFrm.Height = 191
    End If

    originally posted by Gops

    The code I previously posted works a wonder if the cell is formatted mm/dd/yy. My question is, how do I get it to launch the calendar form if I have more than one date format in my sheet. For instance, a cell formatted yyyy-mmm-dd in my sheet would NOT launch the calendar form unless I changed:
    If Target.NumberFormat = "mm/dd/yy" Then
    If Target.NumberFormat = "yyyy-mmm-dd" Then .

    I ask this because I used the calendar control in a sheet I sent off to my younger brother. He is one of the people in the world who get confused if you have to do more than double click to launch something. :lol: Anyway, one of his minions changed the date format on a cell and I got the "Your spreadsheet is broken" call.

    He e-mailed it, I uncovered what the problem was, and am now trying to fix it easily. As there are 15 or so predetermined date formats, it would be a bit cumbersome to cycle through all of them.

    I was hoping to mind meld with the collective genius here at OzGrid and figure out a simple way to do the launch when the NumberFormat was any valid date format (including custom month day year formats).

    As per most of my brother's requests, the ease of the solution is inversely proportional to the speed he expects the reply. :grin: Anything I can fix quickly, he could wait days for, anything that stumps me, he needs the day before yesterday. :lol:

  • Hi B,

    How about a Select Case construct for the formats that you are expecting? For example:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
        Select Case Target.NumberFormat
            Case Is = "mm/dd/yy", "yyyy-mmm-dd"
                MsgBox "OK, expected date format"
            Case Else
                MsgBox "Not a valid date format!"
        End Select
    End Sub


  • Code
    Select Case Target.NumberFormat
            Case Is = "m/d/yy", "m/d/yyyy", "mm/d/yy", "mm/dd/yy", "yyyy-mmm-dd"
                If CalendarFrm.HelpLabel.Caption <> "" Then CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height Else CalendarFrm.Height = 191
            Case Else
                MsgBox "Not a valid date format!"
        End Select

    I started with this in the SelectionChange Event. Of course, this flashed the MsgBox whenever a non-date formatted cell was selected so I commented out that line and the calendar form launches nicely when a cell with one of the many formats included in the Case Is = statement is selected.

    Not real sure this is the final answer, but it works around my brother's employee nicely. :cool:

    Thanks for the insight Richie!

  • Quote

    Originally posted by Phil
    How cool is that...you guys are awsome!!!

    Hi Phil:
    Here is some feedback.
    Something that my users are running into.

    They are using your calendar. Works great. Thanks.
    The way it is set up now, as soon as you select a day it inserts the date with the default month and year. The selected date will be correct if you wanted that month and year.

    However, if the user wants a different month or a different year (from the current values), he needs to choose the year and the month before selecting the day.

    Some users have a habit of choosing the day first thus resulting in a "wrong" month and, if you are in January, "wrong" year.

    Is it too much of an effort to modify the code to insert a "select" button which will trigger the date insertion?

    This way, the user can quickly review the required month, year and date before "selecting".

  • Hey Gops,

    This is no trouble at all. In fact I had this feature in a previous version and had moved away from it for some odd reason. However, what you say makes perfect sense and I'll update with the select button.

    Additionally, I want to play around with some radio buttons on the month and year as well as the drop downs to improve that functionality.

    Perhaps I'll get to that today. I'll forward to you the latest when complete.

    (It's to bad the attach feature is not working, sure would be nice in this case.)

    If anyone else would like the new version...Drop a line on this thread or send me an email and I'll forward.

    Best regards to all.


    PS…Maybe we can talk Dave into making this utility available for download somewhere on the Ozgrid site???

  • I'm currently updating this tool and I'd like to add all the functionality possible.

    Along those lines. Gops had raised a great point about calling up the calendar if any cell is selected that is formatted as a date. Derk had responded with...


    If IsDate(target) then

    Which sounded great but it's looking for a cell value not the cell format.

    Does anyone else have any ideas along the same as Drek's?

    As always, any help would be appreciated.


  • Hey Phil,

    I had used Richie's select case to get around my immediate problem for my brother's worksheet and haven't had time to even try Derk's solution. In the meantime, I was pondering using the worksheet function CELL in the select case structure as the "format" argument returns D1 to D9 if formatted with the standard date or time formats. Each one could be handled appropriately (time formats wouldn't launch the calendar). Just thinking, haven't had time to fiddle with it yet though.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!