Solved: Calendar control not working

[BlueCactus]

Lucas, I just tried your workbook with XL 2004 on Mac OS X (10.3.9 Panther). Works like a charm!

Thank you!!

Works on Excel X too, with one minor issue. In the form's initialize routine, you need one of my favorite lines of code:
[vba]CalendarFrm.Width = CalendarFrm.Width[/vba]

The Excel X will actually choose to display the whole form, instead of just part of it.

[lucas]

Works on Excel X too, with one minor issue. In the form's initialize routine, you need one of my favorite lines of code:
[vba]CalendarFrm.Width = CalendarFrm.Width[/vba]

The Excel X will actually choose to display the whole form, instead of just part of it.

Thanks for testing it guys. Bluecactus, I don't see a problem with adding that line to the form Initialize. I put it in just before the line:
CreateCal = True

[BlueCactus]

Here is the new enhanced three-mode calendar. I'm posting it here as a courtesy update to my previous file. I'll be cleaning up the code, bug testing and sending to KB in the next day or two, where full instructions will be provided.

Calendar mode is set in Module1.test() by changing the value of dpmode:
mode=0 : Simple calendar - click on a date to return it to the calling code.
mode=1 : Enhanced. Click on date to select, return with 'OK' button.
mode=2 : Event calendar. Click on date to select. Then select zero or more events. Click on 'OK' to return selected date, and selected events.

[7/8/2005: File removed; See post #25]

[MOS MASTER]

I think this thread and the one you were involved in both have provided me - and a few others - with some key information. Very cool.
S

Well glad to here we've both learned new tricks.

[BlueCactus]

OK, I promise this is the last version for this thread. It is now the five-mode calendar. Feature-complete, suits my own purposes extremely well. Code is being washed and on the way to KB...

Features are now accessed through:
[vba]Call DatePick.FillVars([defaultDate][,dpMode][,forceCancel][,displayComments][,eventList])[/vba]
defaultDate Optional as Date. The date to return if user clicks 'OK' without selecting a date. Defaults to current date.
dpMode Optional as Integer. Calendar mode: zero through four. Experiment away! Defaults to zero.
forceCancel Optional as Boolean. Do you want a 'Cancel' button. Defaults to False. 'Cancel' returns date as zero.
displayComments Optional as Boolean. Default False. Displays comments associated with event list items for dpmodes two through four.
eventList Optional as Variant containing Array. Dates as Variant Date in eventList(i,1). Event names in eventList(i,2). Comments in eventList(i,3) LBound(eventList, 1) = 1; LBound(eventList, 2) = 1

Edit: Code removed, see post #38.

[Richardall]

The original question was based on this being an active x replacement for a word document to allow calendar controls for Macs.



I greatly appreciate the code you originally provided and it seems to be working fine as I follow your directions.



Could I ask one more question as the novice I am?



I create a text field names "today" and call the test macro on entry. What code do I need to add and where to fill in the selected date to the field?



Thank you for the superior work, it appears to have been helpful to many.



Richard

[BlueCactus]

I would use a slightly modified version of my original second block of code:
[vba]
' This must go in at the top of the code for your userform.
Dim displayDate As Date

' This was the test() macro. No need to put it elsewhere, so just attach
' it directly to the event of interest.
Private Sub Today_Enter()

Load DatePick

' The following shows how to place the calendar position at the calling control position,
' if that is useful to you.
' DatePick.startupposition = 3
' DatePick.Left = Today.Parent.Left + Today.Left
' DatePick.Top = Today.Parent.Top + Today.Top

' Note: If date not supplied to DatePick, current date will be used.
Call DatePick.FillVars()
DatePick.Show

' In mm/dd/yyyy form. Mix and match as desired.
Today.Text = DatePart("m", displayDate) & "/" & DatePart("d", displayDate) & "/" & DatePart("yyyy", displayDate)
End Sub

' This must be included somewhere in the userform code. Must NOT be
' marked Private
Sub returnDate(dD As Date)
displayDate = dD
End Sub[/vba]

I'm glad the code works for you. I realize that it's expanded considerably more than you needed in the past few days - your question just gave me an excuse to embark on a small project that I've had waiting for a while.

[Ken Puls]

OK, I promise this is the last version for this thread. It is now the five-mode calendar. Feature-complete, suits my own purposes extremely well. Code is being washed and on the way to KB...

Just had a chance to play with this. I'm going to be needing something similar to work on 97 and 2003 on two different platforms. I may try activeX still, but was intrigued here.

At any rate, I found a couple of little bugs that I thought I'd kick up for you...

If you click on the scroll bars for Year/Month (say up), then click find today, then click on the scroll bars again, it doesn't shed the prior value. Ie I clicked up on 2005 to 2006. Click Find Today. That part works. Click up again nad I'm at 2007, not 2006. Issue replicats clicking down as well, on either the month or the day.

Also, on my system, Saturday July 16 is showing up with a white background. Everything else is grey. Seems strange. Now, this is using dpMode = 0 if that makes a difference.

Neat work, though.

[Ken Puls]

Also, on my system, Saturday July 16 is showing up with a white background. Everything else is grey. Seems strange.

Until you read the code it does. Didn't realize that was the date fed into the procedure by the calling module...

I realy like the robustness of this code!

[Ken Puls]

Until you read the code it does. Didn't realize that was the date fed into the procedure by the calling module...

Actually, I'm not totally out to lunch here...

When I click on the "Find Today" button, it does not change the white box to be "today". It just activates the current month.

Also, fwiw, if you feed a date to the routine, hitting Okay will only return that date. You won't ever get a 0 (cancel) returned. For my own purposes, I'd like a Cancel button, as I believe that you always should give users a way out.

I've tried modes 0 and 1 now, feeding them an opening date, and the issues are the same on either.

Btw, I've decided that I'm using it for my stuff here as well.

[BlueCactus]

Thank you, kpuls. Greatly appreciate the testing. A large part of the reason I posted the updated code here.

If you click on the scroll bars for Year/Month (say up), then click find today, then click on the scroll bars again, it doesn't shed the prior value. Ie I clicked up on 2005 to 2006. Click Find Today. That part works. Click up again nad I'm at 2007, not 2006. Issue replicats clicking down as well, on either the month or the day.

Now fixed. Nice catch. 'Find Today' was setting ComboBox1.ListIndex and ComboBox2.ListIndex, but I forgot to have it set SpinButton1.Value and SpinButton2.Value.

Also, fwiw, if you feed a date to the routine, hitting Okay will only return that date. You won't ever get a 0 (cancel) returned. For my own purposes, I'd like a Cancel button, as I believe that you always should give users a way out.

This is an additional feature in the version posted above. Set the parameter after dpMode to True, and a cancel button will be generated. Hitting cancel returns displayDate = 0, so that requires an additional test in the calling code.

Ah, I get it now. July 16. A feature, not a bug. But maybe i) a different format (e.g., colored border) would be better; ii) I need to include the option for no default selection. A little preliminary work on that has brought up a potential eventMatrix bug I need to fix too....

Edit: Code now updated to be no pre-selected date by default. Associated issues with eventMatrix also cleared up.

Edit2: Modified code attached. Fixes above bug and changes defaults to i) no date pre-selected; ii) cancel button enabled.

Edit3: Code removed, see post #38.

[Ken Puls]

Cool! I'll download and see how it works here...

[Ken Puls]

Nice! Seems to have the issues I've ecountered.

Question though... I was sort of expecting that when I clicked "find today" it would actually set the value to today's date (and therefore highlight it) instead of just activating that month. Just a preference issue, but curious to know you're thoughts there.

Also, in a perfect world, the two routines: returnDate and returnCodes would be in the userform code somewhere. You'd then be able to just drop the userform into your project and code a one/two liner from another module to show it. I did try throwing them in with the userform code, though, and it didn't seem to work quite right. That was the last version though...

[BlueCactus]

Question though... I was sort of expecting that when I clicked "find today" it would actually set the value to today's date (and therefore highlight it) instead of just activating that month. Just a preference issue, but curious to know you're thoughts there.

Yeah, I've thought about it, and decided that I don't want to do it that way. My primary motivation for including it was to have a 'quick return' if you get off into the depths of time travel. If you want to implement it yourself, then place the following line after the canUpdate decrement in CommandButton2_Click:
[vba] Call SetNewDate(DatePart("d", Date))[/vba]

Also, in a perfect world, the two routines: returnDate and returnCodes would be in the userform code somewhere. You'd then be able to just drop the userform into your project and code a one/two liner from another module to show it. I did try throwing them in with the userform code, though, and it didn't seem to work quite right. That was the last version though...

The reason I did it this way was to avoid declaring the variables as public. But yeah, it has the potential to be a bit of a pain. I could probably at least combine returnDate and returnCodes into one Sub. Maybe have two routines. i) The equivalent of test() ii) Combination of returnDate and returnCodes. Drop both in at the end of a module, and use something like newDate = testEquiv(dpMode, etc, etc) from the calling code.

[Richardall]

Especially BlueCactus.

The document is complete with the needed code for the calendar.

Should I mark the thread closed???? It appears to still be getting a lot of usefull comments and suggestions. I wouldn't want to close out anything that was still usefull.

Admin, please advise.

Richard

[Ken Puls]

Hi Richard,

Please do mark it solved. The discussions can certainly continue anyway, and probably will, but we like knowing that the original issue has been answered. It also makes the thread appear more attractive to someone in a search.

Thanks for posting your status and the question!

[Ken Puls]

Quick update on this one. Today I released a new addin at work which makes use of this calendar, and it rocks! My machine is XL2003 on Windows XP, and the server that everyone else is using is Windows 2003 using XL97 SR-2.

It worked flawlessly all the way around. Probably saved me a bunch of time fooling around with the ActiveX calendar controls.

[BlueCactus]

Revision 13 for your coding pleasure. This incorporates several bug fixes, particularly if you use dpModes 2 ,3, 4. dpModes 0, 1 are affected most when supplying default dates.

Code Access:

1. Transitioning to a userform + code module structure. Calling code now in 'YourCodeHere'. This code currently covers all possibilties, and is longer than would be necessary for most applications. For example, the simplest call would now be myDate = getUserDate() which would use dpMode 0 and all of the standard presets. This will allow users to keep the junk out of their own code, and also allows DatePick to directly reference the calling module ('CalendarModule') to help resolve any potential conflicts.

Re-Enabled Features:

1. dpMode = 3 now has MultiSelect re-enabled in the events ListBox. (This was previously disabled when the code was updated to use eventList comments.) This required code attached to ListBox1.Change() to detect, interpret, and record a history of selection changes in ListBox1 so that the comments could always be displayed for the most recent addition to the ListBox1 selection.

Bug Fixes:

1. Inability to scroll ListBox1 with dpMode = 3 and displayComments = False
2. Subscript out of Range errors when using dpMode >1 and defaultDate not from current year.
3. defaultDate is now set to zero if it is initialized with a year outside of the values of ComboBox2. This fixes several outstanding issues.
4. Several formatting bugs that would appear if defaultDate differed from the current year.
5. A few other minor issues with eventList and eventMatrix.

Foundation changes:

1. Recoding to allow future control over the allowable year range.
2. Combining returnDate() and returnCodes()
3. Recoding to allow future access to secondary features such as calendar size, 'Find Today' behavior, etc.

Enjoy, BC.

Edit: Code removed. Go to post #39.

[BlueCactus]

Revision 16:

Code Access:

1. Using the calendar is now split into two calls. A mandatory one to actually do the work, and an optional pre-call to set up some of the extended features. See the code in module 'YourCodeHere' for details.

Extended Features:

1. Last biggie feature. You can now supply a range of years in which to operate the calendar. For example, you can set it to 1960 through 1980 if you wish. See code in module 'YourCodeHere' for details. I'm particularly interested in any bugs associated with this rehash. This feature has priority over defaultDate. i.e., If defaultDate is not within the calendar range, it will be reset to zero.
2. For kpuls. Optional date selection with 'Find Today'. See code in module 'YourCodeHere' for details.

Bug Fixes:

1. Fixed a new bug that prevented returning a date with dpMode <2.
2. Assorted parameter bugs.

[hahausername]

just wanted to say thanks.