Damn ActiveX Calendar

[Al]

Well I simply cannot get any of the activex calendars to work on the
computers at work - no matter how I try to fiddle!!

I am now trynig to think of a work around for my form. The user needs to be
able to pick a date that will 99% of the time be less than 7 days from the
current date. Needs to show days of the week as this informaiton is relevant
too.

Was thinking of simply populating a listbox with the next thirty days, but
anyone else have a better idea?

TIA

-Al

 

[Jezebel]

The calendar control has always been a problem; and you need to bear in mind
also that UserForms are different to forms in (eg) VB proper -- some
controls just don't work on them.

A listbox sounds like a reasonable strategy. The DatePicker control works OK
for me on Word UserForms. Or you could roll your own using a textbox and
spin buttons.

 

[Al]

Roll my own sounds like a challenge!!

The calendar control has always been a problem; and you need to bear in mind
also that UserForms are different to forms in (eg) VB proper -- some
controls just don't work on them.

A listbox sounds like a reasonable strategy. The DatePicker control works OK
for me on Word UserForms. Or you could roll your own using a textbox and
spin buttons.

 

[Jezebel]

Not so hard. The integer portion of a datetime variable is the date itself,
so you only need add or subtract one to add or subtract one day.

 

[Mark Tangard]

Al,

Depending on how cute you want the form (and how much overhead you're
willing to cook into it), you might consider placing 7 (or a few more)
commandbuttons on the form, having the Initialize event caption them
with the appropriate dates from the current day, and adjust their Click
event code to do what you need. If you think commandbuttons are too
brassy for that purpose, *label* controls also work for this (they have
a Click event just like commandbuttons), and you can adjust their
appearance with the Special Effect property to achieve several degrees
of subtlety.

Granted, it's not very elegant at base, but the effect is pleasant, and
you're right about the calendar controls. I've never gotten one to work
consistently.

 

[Jonathan West]

The approach I take to this is slightly different. I have 3 labels and three
spinbutton controls on the form, one each for day, month and year, each
spinputtin being positioned so it is oriented vertially and is next to its
label. The labels are called lDay, lMonth and lYear, and the spinbuttons are
spDay, spMonth and spYear.

On the form, I have a module-level variable called mDate, which is of type
Date, which hold the date which is displayed. The code to control it all is
as follows.

Private Sub UserForm_Initialize()
mDate = Now
SetDate mTerminationDate, lbTerminationDay, lbTerminationMonth,
lbTerminationYear
End Sub

Private Sub spDay_SpinUp()
mDate = DateAdd("d", 1, mDate)
SetDate mDate, lbDay, lbMonth, lbYear
End Sub

Private Sub spDay_SpinDown()
mDate = DateAdd("d", -1, mDate)
SetDate mDate, lbDay, lbMonth, lbYear
End Sub

Private Sub spMonth_SpinUp()
mDate = DateAdd("m", 1, mDate)
SetDate mDate, lbDay, lbMonth, lbYear
End Sub

Private Sub spMonth_SpinDown()
mDate = DateAdd("m", -1, mDate)
SetDate mDate, lbDay, lbMonth, lbYear
End Sub

Private Sub spYear_SpinUp()
mDate = DateAdd("yyyy", 1, mDate)
SetDate mDate, lbDay, lbMonth, lbYear
End Sub

Private Sub spYear_SpinDown()
mDate = DateAdd("yyyy", -1, mDate)
SetDate mDate, lbDay, lbMonth, lbYear
End Sub


Private Sub SetDate(dDate As Date, lDay As Label, lMonth As Label, lYear As
Label)
lDay.Caption = Format(dDate, "d")
lMonth.Caption = Format(dDate, "MMMM")
lYear.Caption = Format(dDate, "yyyy")
End Sub

The UserForm_Initialize routine sets the date to today, and calls the
SetDate routine to set the labels as appropriate.

Each of the spinputton routines increases or decreases the date by the
appropriate interval, and then calls the SetDate routine to update the
labels.

There are four things about this that I like.

- it doesn't require a reference to an external DLL or OCX
- it doesn't require very much code.
- it is impossible to set an invalid date (Feb 31st for instance)
- When you spin past the end of a month or year, the whole date is properly
incremented or decremented, i.e. going up a day from Jan 31 takes you to Feb
1.

--
Regards
Jonathan West - Word MVP
http://www.multilinker.com
Please reply to the newsgroup

 

[Jean-Guy Marcil]

Hi Jonathan,

I tried and loved your idea!

Two little questions though:

What are the "Termination" thingy in the following line:
SetDate mTerminationDate, lbTerminationDay, lbTerminationMonth, _
lbTerminationYear

In the Spin button code, where do you get the values for lbDay, lbMonth and
lbYear so as to pass them to SetDate, which does not seem to need them to
work properly anyway....
mDate = DateAdd("d", 1, mDate)
SetDate mDate, lbDay, lbMonth, lbYear

When I first tried your code, it did not work at all because of the two
questions above, I had to modify it like in the following code, then
everything ran smoothly.

'_______________________________________
Option Explicit
Dim mdate As Date
'_______________________________________

Private Sub UserForm_Initialize()

mdate = Now
SetDate mdate
End Sub
'_______________________________________

Private Sub spDay_SpinUp()
mdate = DateAdd("d", 1, mdate)
SetDate mdate
End Sub
'_______________________________________

Private Sub spDay_SpinDown()
mdate = DateAdd("d", -1, mdate)
SetDate mdate
End Sub
'_______________________________________

Private Sub spMonth_SpinUp()
mdate = DateAdd("m", 1, mdate)
SetDate mdate
End Sub
'_______________________________________

Private Sub spMonth_SpinDown()
mdate = DateAdd("m", -1, mdate)
SetDate mdate
End Sub
'_______________________________________

Private Sub spYear_SpinUp()
mdate = DateAdd("yyyy", 1, mdate)
SetDate mdate
End Sub
'_______________________________________

Private Sub spYear_SpinDown()
mdate = DateAdd("yyyy", -1, mdate)
SetDate mdate
End Sub
'_______________________________________

Private Sub SetDate(dDate As Date)
lDay.Caption = Format(dDate, "d")
lMonth.Caption = Format(dDate, "MMMM")
lYear.Caption = Format(dDate, "yyyy")
End Sub
'_______________________________________

TIA

--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jonathan West]

Hi Jonathan,

I tried and loved your idea!

Two little questions though:

What are the "Termination" thingy in the following line:
SetDate mTerminationDate, lbTerminationDay, lbTerminationMonth, _
lbTerminationYear

Ah, I pulled the code from a current project where I needed separate start
and end dates. I forgot to remove the termination parts of the names from
that bit of the code.

In the Spin button code, where do you get the values for lbDay, lbMonth and
lbYear so as to pass them to SetDate, which does not seem to need them to
work properly anyway....
mDate = DateAdd("d", 1, mDate)
SetDate mDate, lbDay, lbMonth, lbYear

The way my code was working was that it passed the date which had to be
displayed and also the 3 labels on which it should be displayed. That
enabled the same SetDate routine to update multiple dates.

When I first tried your code, it did not work at all because of the two
questions above, I had to modify it like in the following code, then
everything ran smoothly.

'_______________________________________
Option Explicit
Dim mdate As Date

That should be

Private mDate as Date

I'd recommend always using Public or Private to declare variables outside
individual routines, so there is no possible confusion as to whether they
are module or global in scope.

 

[Jean-Guy Marcil]

'_______________________________________

That should be

Private mDate as Date

I'd recommend always using Public or Private to declare variables outside
individual routines, so there is no possible confusion as to whether they
are module or global in scope.

Of course, that makes a lot of sense!

Thanks for the tip.

 

[SafetyGuy]

I need to create a vacation request form in Word that allows a user to "pick" the start date and the end date of their vacation. The calendar control didn't work for me

I should say that I am totally inexperienced using ActiveX controls or Visual Basic. If anyone could help, I would appreciate it.