PDA

View Full Version : Solved: Calendar control not working



Richardall
06-30-2005, 01:24 PM
I have built a Word form that includes a field that that holds a date.

When you tab into the field, a response box pops up and asks "today" yes or no. If the answer is yes, the field is filled with today?s date. If no, a drop down calendar (frmCalendar.show) appears and the date can be chosen from that calendar.

So with some help from the Word help forum, I got the form to work. The problem is that it won't work on a Mac. The intent is to use mostly on PCs but there will be times that the file will be needed on the mac. I would also like to use this funtion on some other forms.

Can the calendar control work for the Mac? Could some VB code solve the problem? Is there any way one solution could work for both Mac and PC? Are there any suggestions?

Any suggestions and help are appreciated.

shades
06-30-2005, 02:50 PM
It's been a while, but I think that Calendar is not supported on the Mac.

Jim McGimpsey's Response (http://groups-beta.google.com/group/microsoft.public.mac.office.excel/browse_thread/thread/368244d32f7c8519/83a8b03761b61e8c?q=ActiveX+Calendar&rnum=1#83a8b03761b61e8c): It is an ActiveX control, which is not supported on Macs.

Jim is an MVP for Office for Mac.

BlueCactus
06-30-2005, 10:08 PM
shades is correct. Calendar is an ActiveX thingie. No ActiveX thingies are supported in any Mac app that I'm aware of.

So, this one will require a bit of work. Might not actually be too hard to design a new tool from scratch. A weekend project, maybe. A stop gap measure might be to provide a form with month; day; year comboboxes for Mac users.

Richardall
07-01-2005, 05:00 AM
I barely know aht and activieX control is and I doubt that I have a clue how to desing the combo box you mentioned. But I'm willing to learn!

I understand that both of you (Shades & BlueCactus) are the rainining experts for the Mac based question. Can you help?

If this to much to ask, I understand. Can you perhaps help me put in some code that will notify the Mac users that this filed won't work and to enter the date manually?

I'm completely new to the Forms and code but I would like to learn. At this point, the project meets the need and I can close the project, but I still think it could be better in the long run.

Any help would be appreciated.

Ricahrd

BlueCactus
07-04-2005, 12:57 AM
Well, this provided a good opportunity for tinkering for my own benefit. I needed:
1) to work out how the hell VBA handles dates
2) to generate a calendar 'control' that I can later customize.

I may be reinventing the wheel here, but I don't care. It's a learning experience.

So, let me know what you think. I cannot upload a file right now, so this will require a little attention to detail. (Hint: double-clicking a toolbox button will allow you to keep dropping controls on a form without having to return to the toolbox each time.)

1. Insert a new form.
2. Name it 'DatePick'
3. Onto it, drop the following:

37 TextBoxes
8 text Labels
2 ComboBoxes
2 SpinButtons
1 CommandButton

Do not set names, positions or sizes - the code will take care of that.
4. Take a breath. (And drink a beer).
5. Double-click on the form, and replace ALL code with the following:
Dim dayNames As Variant, monthNames As Variant, daysMonth As Variant
Dim canUpdate As Integer, selectDate As Date

Sub FillVars(Optional sD As Date = 0)
Dim i As Integer, thisBox, thisLabel
Dim posX As Integer, posY As Integer, widthDay As Integer, heightDay As Integer

dayNames = Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
monthNames = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
daysMonth = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

If sD = 0 Then
selectDate = Date
Else
selectDate = sD
End If

canUpdate = 1

widthDay = 30
heightDay = 20

posX = widthDay / 2

With DatePick.Controls("Label8")
.Top = heightDay / 2
.Left = widthDay / 2
.Font.Size = 16
.Font.Bold = True
.Caption = monthNames(DatePart("m", Date) - 1) & " " & DatePart("yyyy", Date)
.Height = heightDay * 1.5
.Width = 150
End With

For i = 1 To 7
Set thisLabel = DatePick.Controls("Label" & i)

With thisLabel
.Caption = dayNames(i - 1)
.Font.Size = 10
.Font.Bold = True
.Top = heightDay * 2
.Left = posX
.Width = widthDay
.Height = heightDay
End With

posX = posX + widthDay
Next i

posX = widthDay / 2
posY = heightDay * 3

For i = 1 To 37
Set thisBox = DatePick.Controls("TextBox" & i)

With thisBox
.Top = posY
.Left = posX
.Width = widthDay
.Height = heightDay
End With

If i / 7 = Int(i / 7) Then
posX = widthDay / 2
posY = posY + heightDay
Else
posX = posX + widthDay
End If
Next i

posY = posY + heightDay

With ComboBox1
.Left = widthDay / 2
.Top = posY + heightDay / 2
.Width = widthDay * 3.5
.Height = heightDay

For i = 1 To 12
.AddItem monthNames(i - 1)
Next i
.ListIndex = DatePart("m", Date) - 1
.Style = fmStyleDropDownList
End With

With ComboBox2
.Left = widthDay * 5
.Top = posY + heightDay / 2
.Height = heightDay
.Width = widthDay * 2

For i = -9 To 10
.AddItem DatePart("yyyy", Date) + i
Next i
.ListIndex = 9
.Style = fmStyleDropDownList
End With

With SpinButton1
.Left = widthDay * 4
.Top = posY + heightDay / 2
.Height = heightDay
.Width = widthDay / 2
.Min = 0
.Max = 11
.Value = ComboBox1.ListIndex
End With

With SpinButton2
.Left = widthDay * 7
.Top = posY + heightDay / 2
.Height = heightDay
.Width = widthDay / 2
.Min = 0
.Max = 19
.Value = ComboBox2.ListIndex
End With

With CommandButton1
.Caption = "Cancel"
.Top = posY + heightDay * 2
.Height = heightDay
.Left = widthDay * 5.5
.Width = widthDay * 2
.Font.Size = 12
End With

With DatePick
.Width = 8 * widthDay
.Height = CommandButton1.Top + 2.5 * heightDay
.Caption = "Choose Date"
End With

Call SetDays(Date)

canUpdate = canUpdate - 1
End Sub

Private Sub ComboBox1_Change()
If canUpdate = 0 Then
canUpdate = canUpdate + 1
Label8.Caption = ComboBox1.Text & " " & ComboBox2.Text
Call SetDays(DateValue(ComboBox1.Text & " 1, " & ComboBox2.Text))
SpinButton1.Value = ComboBox1.ListIndex
canUpdate = canUpdate - 1
End If
End Sub

Private Sub ComboBox2_Change()
If canUpdate = 0 Then
canUpdate = canUpdate + 1
Label8.Caption = ComboBox1.Text & " " & ComboBox2.Text
Call SetDays(DateValue(ComboBox1.Text & " 1, " & ComboBox2.Text))
SpinButton2.Value = ComboBox2.ListIndex
canUpdate = canUpdate - 1
End If
End Sub

Private Sub CommandButton1_Click()
Unload DatePick
End Sub

Private Sub SpinButton1_Change()
ComboBox1.ListIndex = SpinButton1.Value
End Sub

Private Sub SpinButton2_Change()
ComboBox2.ListIndex = SpinButton2.Value
End Sub

Private Sub TextBox1_enter()
Call SetNewDate(TextBox1.Text)
End Sub
Private Sub TextBox2_enter()
Call SetNewDate(TextBox2.Text)
End Sub
Private Sub TextBox3_enter()
Call SetNewDate(TextBox3.Text)
End Sub
Private Sub TextBox4_enter()
Call SetNewDate(TextBox4.Text)
End Sub
Private Sub TextBox5_enter()
Call SetNewDate(TextBox5.Text)
End Sub
Private Sub TextBox6_enter()
Call SetNewDate(TextBox6.Text)
End Sub
Private Sub TextBox7_enter()
Call SetNewDate(TextBox7.Text)
End Sub
Private Sub TextBox8_enter()
Call SetNewDate(TextBox8.Text)
End Sub
Private Sub TextBox9_enter()
Call SetNewDate(TextBox9.Text)
End Sub
Private Sub TextBox10_enter()
Call SetNewDate(TextBox10.Text)
End Sub
Private Sub TextBox11_enter()
Call SetNewDate(TextBox11.Text)
End Sub
Private Sub TextBox12_enter()
Call SetNewDate(TextBox12.Text)
End Sub
Private Sub TextBox13_enter()
Call SetNewDate(TextBox13.Text)
End Sub
Private Sub TextBox14_enter()
Call SetNewDate(TextBox14.Text)
End Sub
Private Sub TextBox15_enter()
Call SetNewDate(TextBox15.Text)
End Sub
Private Sub TextBox16_enter()
Call SetNewDate(TextBox16.Text)
End Sub
Private Sub TextBox17_enter()
Call SetNewDate(TextBox17.Text)
End Sub
Private Sub TextBox18_enter()
Call SetNewDate(TextBox18.Text)
End Sub
Private Sub TextBox19_enter()
Call SetNewDate(TextBox19.Text)
End Sub
Private Sub TextBox20_enter()
Call SetNewDate(TextBox20.Text)
End Sub
Private Sub TextBox21_enter()
Call SetNewDate(TextBox21.Text)
End Sub
Private Sub TextBox22_enter()
Call SetNewDate(TextBox22.Text)
End Sub
Private Sub TextBox23_enter()
Call SetNewDate(TextBox23.Text)
End Sub
Private Sub TextBox24_enter()
Call SetNewDate(TextBox24.Text)
End Sub
Private Sub TextBox25_enter()
Call SetNewDate(TextBox25.Text)
End Sub
Private Sub TextBox26_enter()
Call SetNewDate(TextBox26.Text)
End Sub
Private Sub TextBox27_enter()
Call SetNewDate(TextBox27.Text)
End Sub
Private Sub TextBox28_enter()
Call SetNewDate(TextBox28.Text)
End Sub
Private Sub TextBox29_enter()
Call SetNewDate(TextBox29.Text)
End Sub
Private Sub TextBox30_enter()
Call SetNewDate(TextBox30.Text)
End Sub
Private Sub TextBox31_enter()
Call SetNewDate(TextBox31.Text)
End Sub
Private Sub TextBox32_enter()
Call SetNewDate(TextBox32.Text)
End Sub
Private Sub TextBox33_enter()
Call SetNewDate(TextBox33.Text)
End Sub
Private Sub TextBox34_enter()
Call SetNewDate(TextBox34.Text)
End Sub
Private Sub TextBox35_enter()
Call SetNewDate(TextBox35.Text)
End Sub
Private Sub TextBox36_enter()
Call SetNewDate(TextBox36.Text)
End Sub
Private Sub TextBox37_enter()
Call SetNewDate(TextBox37.Text)
End Sub

Private Sub SetNewDate(setDay As Integer)
If canUpdate = 0 Then
Call returnDate(DateValue(ComboBox1.Text & " " & setDay & ", " & ComboBox2.Text))
Unload DatePick
End If
End Sub

Private Sub UserForm_Initialize()
End Sub

Sub SetDays(tempDate As Date)
Dim i As Integer, Day, numDay As Integer

tempDate = DateValue(monthNames(DatePart("m", tempDate) - 1) & " 1, " & DatePart("yyyy", tempDate))

numDay = DatePart("w", tempDate)

If DatePart("yyyy", tempDate) / 4 = DatePart("yyyy", tempDate) \ 4 Then
daysMonth(1) = 29
Else
daysMonth(1) = 28
End If

If numDay > 1 Then
For i = 1 To numDay - 1
DatePick.Controls("TextBox" & i).Enabled = False
DatePick.Controls("TextBox" & i).Visible = False
Next i
End If

For i = 1 To daysMonth(DatePart("m", tempDate) - 1)
With DatePick.Controls("TextBox" & (numDay + i - 1))
.Visible = True
.Enabled = True
.Text = i
.Locked = True
.TabStop = False
.BackStyle = fmBackStyleTransparent
.ForeColor = RGB(0, 0, 0)
.Font.Bold = False
If DatePart("yyyy", Date) = ComboBox2.Text And monthNames(DatePart("m", Date) - 1) = ComboBox1.Text Then
If DatePart("d", Date) = i Then
.ForeColor = RGB(255, 0, 0)
.Font.Bold = True
End If
End If
If DatePart("yyyy", selectDate) = ComboBox2.Text And monthNames(DatePart("m", selectDate) - 1) = ComboBox1.Text Then
If DatePart("d", selectDate) = i Then .BackStyle = fmBackStyleOpaque
End If
End With
Next i

For i = daysMonth(DatePart("m", tempDate) - 1) + numDay To 37
With DatePick.Controls("TextBox" & i)
.Enabled = False
.Visible = False
End With
Next i
End Sub

6. Insert a Module into the project.
7. Paste the following code into the new module:
Dim displayDate As Date

Sub test()
displayDate = DateValue("July 16, 2005")

Load DatePick

' If this code is in a form, the following shows how to place the calendar position at the calling control
' DatePick.startupposition = 3
' DatePick.Left = UserForm1.Left + CommandButton1.Left
' DatePick.Top = UserForm1.Top + CommandButton1.Top

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

MsgBox "American notation: " & DatePart("m", displayDate) & "/" & DatePart("d", displayDate) & "/" & DatePart("yyyy", displayDate)
End Sub

Sub returnDate(dD As Date)
displayDate = dD
End Sub
8. To test, run the macro named test()

Notes: 1) The date supplied to the form is highlighted in white. 2) The current date is highlighted in red. 3) Allowable year range can easily be expanded if needed. 4) The code can almost certainly be shortened. I'm still learning how to deal with dates. 5) If you use it in your app, test it well. Date handling is notoriously prone to unexpected bugs.

This form should work on Win VBA too, but I have not tested it there. The following code will allow you to do different stuff on Win or Mac:

If Instr(Application.OperatingSystem, "Windows") Then
' do Win VBA stuff
Else
' do Mac VBA stuff
End If

Scottie P
07-04-2005, 08:23 AM
:ole: "KB - KB - KB!!"

BlueCactus
07-04-2005, 06:35 PM
Yeah, I'll send it that way soon. I need to tidy up a few loose ends first. Some of the code can be simplified / shortened. I originally wanted to make it a programatically constructed form (like brettdj's colour form), but I think there's too much code to make that worthwhile. I also have a couple of features to add.

shades
07-04-2005, 07:18 PM
Excellent work! I agree, it should be a KB. I ran into another request like this just last week. Very timely!

Scottie P
07-04-2005, 08:21 PM
Yes, it does work with Win Platform.
I set this up (WinXP Pro + Office XP Pro), took all of about 2-3 minutes with copy then paste + paste + paste for the Txtb^s and it ran nicely.

Take this one and run with it BC - as Shades has already mentioned, a Calendar Control variety for MAC is a sought after item (ran into that myself this past Saturday) and this would be a super addition to the Kb.

I think it's worth waiting for, indeed! :bow:

Can't wait to see what you do with it as a final work! Very nice! :friends:

Very cool!

BlueCactus
07-05-2005, 01:14 AM
I'm glad to see that you guys think this is worth it.

Current version includes the following improvements:
A. Default date selection displayed in a new label.
B. Inclusion of a 'Find Today' button to help with long range calendar navigation.
C. Now operates in three modes:
DatePick(defaultDate as Date, mode as Integer, eventList as Variant)
mode = 0

operates same as code above.
mode = 1

similar to mode 0, but picking a date just changes the date selection label. The Cancel button is now an OK button and is used to confirm the selection.
mode = 2 (under development, may or may not make it to KB)

indexes the day-of-the-month textboxes to event data stored in an array. Total # of events for given day is listed under each textbox. Selecting a day lists that day's events to a listbox. One or more events may be selected and sent back to the calling code.

The reason for mode 2 is that my spreadsheet maintains meta data concerning other files, part of which is a date stamp. I want to be able to choose files based on the calendar rather than the directory hierarchy.

If anyone has any desired features, I'll be glad to consider them (but obviously I won't promise them. :devil: )

Richardall
07-05-2005, 05:48 AM
I'll follow your directions and try to incorporate this into a copy of my working file. If it works well, I'll be very thankful.

It also looks like many other will prosper form your work.

Nice job and thank you,

lucas
07-06-2005, 08:21 AM
Hey guys,
I found this some time back and have been using it exclusivly. Even in Windows the native calendar control is not portable if they don't have the control in the system where you move it.

Works on sheet code but only pops up if you click a cell with a specific format.
might work on mac-don't know but worth a look.

This one is for excel

MOS MASTER
07-06-2005, 12:10 PM
Hey guys,
I found this some time back and have been using it exclusivly. Even in Windows the native calendar control is not portable if they don't have the control in the system where you move it.

Works on sheet code but only pops up if you click a cell with a specific format.
might work on mac-don't know but worth a look.

This one is for excel
Hi, :yes

What a beauty! Please do submit this one as well for a kb entry? :whistle:

lucas
07-06-2005, 12:40 PM
Hi, :yes

What a beauty! Please do submit this one as well for a kb entry? :whistle:

Thanks Joost I have had a lot of opportunities to use this and everyone likes it. The best part is its portability. I will submit it to the kb

shades
07-06-2005, 12:48 PM
I won't be able to try this on the Mac until this evening. But looks good in XL 2003 on Win 2K.

MOS MASTER
07-06-2005, 01:27 PM
Thanks Joost I have had a lot of opportunities to use this and everyone likes it. The best part is its portability. I will submit it to the kb
Exactly portabillity is the key word!

I've learned only this week from Shades & Blue Cactus in this topic that a mac doesn't support ActiveX (Btw I loved the other entry as well!...more work to try) :*)

But I've never worked on a Mac so I didn't mis it...but it will come in handy one day for sure.

Later..:whistle:

Richardall
07-06-2005, 01:32 PM
Bluecactus, Thank you again for all the help. In one of your original replies, you mentioned that you couldn?t upload the file. Has that changed? Can you wrap this up and post it. I did what you said and I'm having some success, but...



Was wondering?



Thank you again.

Scottie P
07-06-2005, 03:06 PM
Exactly portabillity is the key word!
I've learned only this week from Shades & Blue Cactus in this topic that a mac doesn't support ActiveX (Btw I loved the other entry as well!...more work to try) :*)
But I've never worked on a Mac so I didn't mis it...but it will come in handy one day for sure.


I am with you MM - never worked on a MAC and didn't realize ActiveX wasn't supported. I actually found out about the ActiveX issue 1 day before the thread started.

I was checking out your TimePicker application in the other thread. That lead me to go on to the Newsgroups to find something else that I had a question about. It was in one of the Newsgroup threads that I found out that MAC does not support the ActiveX feature. Who Knew?

Interesting and I suppose it creates some unique circumstances, but it also leaves open a great opportunity for someone creative with VBA...so maybe it isn't a bad thing :dunno

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

shades
07-06-2005, 04:29 PM
Lucas, I just tried your workbook with XL 2004 on Mac OS X (10.3.9 Panther). Works like a charm!

Thank you!!

BlueCactus
07-06-2005, 05:48 PM
Bluecactus, Thank you again for all the help. In one of your original replies, you mentioned that you couldn?t upload the file. Has that changed? Can you wrap this up and post it.

Here you go. This one contains mode 0 and mode 1 listed above.

You can look at the file, but to use it in VBA requires code like:
Load DatePick
Call DatePick.FillVars
DatePick.Show

Options are through...

DatePick.FillVars(defaultSelection, mode)

defaultSelection (optional, type Date) is the default date to be returned. If unspecified, Date (i.e., today's date) is used.

mode (optional, type Integer) is either 0 or 1 (default 0) in this revision.

mode 0: date returned immediately on clicking a day.
mode 1: date displayed on form when selected. Returned when user clicks 'OK' button. Allows user to select a date and hunt around the calendar some more before confirming the selection.

[7/7/05: File updated to fix a few minor cosmetic issues on Win VBA]
[7/7/05: File removed. Replaced with post #23]

BlueCactus
07-06-2005, 07:03 PM
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:
CalendarFrm.Width = CalendarFrm.Width

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

lucas
07-06-2005, 08:30 PM
Works on Excel X too, with one minor issue. In the form's initialize routine, you need one of my favorite lines of code:
CalendarFrm.Width = CalendarFrm.Width

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

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
07-08-2005, 12:03 AM
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
07-08-2005, 10:01 AM
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. :yes

BlueCactus
07-08-2005, 06:16 PM
:rofl: 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... :giggle

Features are now accessed through:
Call DatePick.FillVars([defaultDate][,dpMode][,forceCancel][,displayComments][,eventList])
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
07-11-2005, 08:05 AM
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
07-11-2005, 08:25 AM
I would use a slightly modified version of my original second block of code:

' 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

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.
:beerchug:

Ken Puls
07-12-2005, 01:14 PM
:rofl: 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... :giggle

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. :yes

Ken Puls
07-12-2005, 01:42 PM
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.:doh: Didn't realize that was the date fed into the procedure by the calling module... :blush

I realy like the robustness of this code! :yes

Ken Puls
07-12-2005, 03:32 PM
Until you read the code it does.:doh: Didn't realize that was the date fed into the procedure by the calling module... :blush

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. :yes

BlueCactus
07-12-2005, 07:01 PM
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. :doh:


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
07-13-2005, 11:04 AM
Cool! I'll download and see how it works here... :)

Ken Puls
07-13-2005, 11:14 AM
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
07-13-2005, 11:33 AM
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:
Call SetNewDate(DatePart("d", Date))


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
07-14-2005, 12:46 PM
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
07-14-2005, 01:11 PM
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. :yes

Thanks for posting your status and the question!

Ken Puls
07-14-2005, 10:17 PM
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. :yes

BlueCactus
07-15-2005, 11:46 PM
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
07-17-2005, 08:50 AM
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
10-29-2007, 03:44 PM
just wanted to say thanks.

tomzanca
02-01-2012, 03:17 AM
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
05-25-2012, 11:49 PM
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.

Private Sub TextBox20_enter(): Call SetNewDate(TextBox20.Text): End Sub

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.

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

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
07-31-2012, 05:36 PM
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
08-04-2012, 09:11 PM
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
09-10-2015, 05:24 AM
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
12-22-2015, 12:49 PM
Solved?

LS_Qwertt
12-22-2015, 01:48 PM
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
03-18-2016, 11:43 AM
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!