VBA Calendar Control without Control

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:

Will

(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

This tool was pretty impressive prior to any enhancements. I can hardly wait to get the updated version. :biggrin:

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:

:usaflag:

Just got a copy from Brandtrock the other day. This is an awesome tool Phil!

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
CalendarFrm.Show
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:

Quote

Originally posted by Brandtrock
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
CalendarFrm.Show
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:

Display More

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
frmCaution.Show

end if
end if
end if

originally posted by Brandtrock

Quote

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

originally posted by Gops

Quote

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
frmCaution.Show

end if
end if
end if

Display More

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
to
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

HTH

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
        CalendarFrm.Show
        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!

Use the IsDate function, which will return true for any date entry (as long as it's not empty).
If IsDate(target) then

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".

This control will be available shortly for free download from www.excel-it.com.

Many thanks to Phil for his excellent work!

I would love to try this out. Please post an update when it becomes available.

Many thanks

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.

HTH
:usaflag: