Solved: Calendar control not working

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

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

[tomzanca]

Hi,
i've been trying to use the great code for the calendar control that bluecactus posted on the KB (i'm newbie and I couldn't find a way to post my comment there).
Unfortunately it crashes my excel :-(
And it is the same on all the other Macs i tested it on.

Just for your info I've got:
MacBook pro
Lion
Office 2011 (fully updated and original)

and the log I've got after crashing is:
Microsoft Error Reporting log version: 2.0

Error Signature:
Exception: EXC_BAD_ACCESS
Date/Time: 2012-02-01 09:45:56 +0000
Application Name: Microsoft Excel
Application Bundle ID: com.microsoft.Excel
Application Signature: XCEL
Application Version: 14.1.4.111121
Crashed Module Name: Fm20.dylib
Crashed Module Version: unknown
Crashed Module Offset: 0x0000bf5d
Blame Module Name: Fm20.dylib
Blame Module Version: unknown
Blame Module Offset: 0x0000bf5d
Application LCID: 1040
Extra app info: Reg=en Loc=0x0410
Crashed thread: 0

Don't know if this can help you somehow
thanks anyway for the great job!

[dave_nq]

Hi,

I have the same experience reported by tomzanca, with the calendar control crashing Excel 2011 (Version 14.2.2) on a Mac with Lion OS 10.7.4.

I am using the simplest form of the calendar control (dpMode = 0). Changing the month or year in the DatePick userform works fine, but clicking on a date causes Excel to crash. In stepping though the code, the crash happens when it gets to EndSub in the TextBox_enter subroutine triggered by clicking on one of the dates e.g.

[VBA]Private Sub TextBox20_enter(): Call SetNewDate(TextBox20.Text): End Sub[/VBA]

I think it's associated with the DatePick userform being loaded/unloaded. If in the following code I comment out the Unload Me line, the code exits the TextBox subroutine without a problem, but the date doesn't change to what I selected.

[VBA]Private Sub SetNewDate(setDay As String)
' Change the selected date
If canUpdate = 0 Then
If dpMode = 0 Then
' Quits form with selected date for dpMode 0
Call CalendarModule.returnDate(DateValue(ComboBox1.Text & " " & setDay & ", " & ComboBox2.Text), "no events")
Unload Me
ElseIf dpMode = 1 Then
selectDate = DateValue(ComboBox1.Text & " " & setDay & ", " & ComboBox2.Text)
Call SetDays(selectDate)
Label9.Caption = strLabel9C & ComboBox1.Text & " " & setDay & ", " & ComboBox2.Text
Else
setDay = Left(setDay, InStr(setDay, Chr(13)) - 1)
selectDate = DateValue(ComboBox1.Text & " " & setDay & ", " & ComboBox2.Text)
Call SetDays(selectDate)
Label9.Caption = strLabel9C & ComboBox1.Text & " " & setDay & ", " & ComboBox2.Text

Call ListEvents
End If
End If
End Sub[/VBA]

The Error Reporting log details are as follows:

Microsoft Error Reporting log version: 2.0

Error Signature:
Exception: EXC_BAD_ACCESS
Date/Time: 2012-05-26 06:39:49 +0000
Application Name: Microsoft Excel
Application Bundle ID: com.microsoft.Excel
Application Signature: XCEL
Application Version: 14.2.2.120421
Crashed Module Name: Fm20.dylib
Crashed Module Version: unknown
Crashed Module Offset: 0x0000be89
Blame Module Name: Fm20.dylib
Blame Module Version: unknown
Blame Module Offset: 0x0000be89
Application LCID: 1033
Extra app info: Reg=en Loc=0x0409
Crashed thread: 0

I suspect this is a bug in Excel 2011 for Mac. Any assistance to workaround it would be welcome. I need a portable calendar control to work on Excel 3003 / 2007 / 2010 for WIN and 2011 for Mac, and this would be ideal - if I can get it to work.

[serj1980]

I don't know reason of crash by unload form in Excel 2011, but I can fix it by replacing TextBox1-37 to Labels.
Globalization question, I can resolve by replace all DateValue on DateSerial function. Now it's work for me on Russian and Ukrainian environment.

Good luck.

[dave_nq]

Thanks for the suggestion re replacing TextBoxes with Labels. I've not been working on this project on the mac for a couple of months, but will go back to it to try this fix, and report back. I have been working on a Win project, where this calendar control does work without need for modification.

[LS_Qwertt]

I have the same problem but, since I am a complete newbie, I was not able to fix the issue a per srj1980's indication.

Could you please give some more details, please?

[gert.thys]

Solved?

[LS_Qwertt]

Hello gert.thys,

actually no, I didn't solve it. I tried to replace TextBoxes with Labels but with no joy. Everything was messed up. I am still stuck there...

[xman2000]

hello people!

link to Thread forum by me:
"vbaexpress.com/forum/showthread.php?55438-Calendar-by-BlueCactus-not-works"

iam post the screens of errors and sample file.

if you have this workbook working please share with me!

calendarBluecactus in kb repository not working never!

thanks!