Writing VBA Routine in XL2007 to run on XL2002

[John G.]

I'm having difficulty with the calendar.value method in a userform that's
being used for "beginning and ending dates". I've tried two ways to run the
calendar. The first was to include the calendar control, which I got from
rondebruin.nl , in a userform where it pops up when a button on the same
userform is clicked and I tried another way by creating a separate userform
just for the calendar.

The dates will be initially posted to the startup userform and later put
into a spreadsheet form for printing.

When I do anything programmatically, e.g., setting the calendar.value,
retrieving the calendar.value.etc, I get a runtime error telling me that the
method has failed.
I can load the calendar userform with no problems but everytime I get to a
line such as

Private Sub Calendar1_Click()

StmtDtBegButton.Visible = False
BegDtTxt.Value = Format("BegDtTxt.Value", "mm/dd/yy")
BegDtTxt.Value = Calendar1.Value '<<<<<<
Unload BegDtCal
BegDtTxt.Visible = True
End Sub

The runtime error "-2147319765 (8002802b) : Method 'Value' of object
'_DMsacal70' failed" pops up.

Also, if it's any help, I am not able to change any property values for the
calender such as "Day Font" or "Grid Font" without getting an error msg
"Object library invalid or contains references to object definitions that
could not be found".
I can get into the "Custom Property Page", change the tab and visible methods.

I'm out of ideas on this end. Could use some help. Thx...JG

 

[OssieMac]

Hi John,

Am I correct in assuming that BegDtTxt is a textbox. If so then try the
following.

Private Sub Calendar1_Click()

StmtDtBegButton.Visible = False
BegDtTxt.Value = Format(Calendar1.Value, "mm/dd/yy")
Unload BegDtCal
BegDtTxt.Visible = True
End Sub

 

[OssieMac]

Hi again John,

If I understand your 2nd question correctly then the following might help.
Get the color codes from the calendar properties dialog box.

Private Sub CommandButton1_Click()
With UserForm1.Calendar1
.BackColor = &HFFFF&

'Alternative code for color
'.BackColor = vbYellow

.GridFontColor = &HFF&
.GridLinesColor = &H0&
.TitleFontColor = &HFF0000
.DayFont.Name = "Times New Roman"
.GridFont.Name = "Arial"
.DayFont.Bold = True
End With
End Sub

 

[John G.]

OssieMac...thx for the quick reply.

Tried your suggestion but there's "no joy". Same error msg as before. I've
even DL'd and registered the 11.0 mscal.ocx and get the same msg.

I forgot to mention in my first post...I'm not running anything too exotic,
WIN XP SP3 and Office 2007 Pro SP1.

I also have VS2008 and wrote a VB routine sometime ago where I had a
calendar popup in the same userform as the buttons and didn't have any
problems. Getting the dates into Crystal Reports...that was another
situation.

Again, thx for the suggestion. Anything else would be greatly appreciated.

 

[John G.]

OssieMac...

I'm now sure there's something else going on that may directly involve the
OCX. Tried your latest suggestions and hit the same brickwall as soon as it
entered the first WITH line to change the background color.

Is there any problems running either v10 or v11 of mscal.ocx with the 2007
version of VBA?

Thx for hangin' in there, JG

 

[John G.]

UPDATE: I got to thinking more about the fact that it wasn't my programming
(tho it's not the greatest) that was causing the problem but possibly the OCX.

I unreg'd mscal.ocx and while I was searching the registry (not recommended
for the the faint of heart) I came across a call an mscal.ocx in the Office12
folder, dated 2007. I reg'd that one and have to say there is definitely
"joy in whoville". Trouble is it to damn late right now to do anything about
it.

Thanks for your help...OssieMac

 

[OssieMac]

I hope that you get it working now John. I tested the code I gave you and it
all worked. I am using xl2007 with OS Windows Vista Home Premium.

Your question. "Is there any problems running either v10 or v11 of mscal.ocx
with the 2007 version of VBA?" I don't know the answer to that one.

 

[John G.]

OssieMac...

I got everything working fine once I reg'd v12. In answer to your question,
as far as my system is concerned, neither v10 or v11 worked in XL2007.

Once I loaded the v12 Calendar, all the calendar1 call worked fine and the
property window for the calendar was fully populated and accessible.

The next trick will be to find out if I'll be able to use v10 on the 2002
machine that this routine is targeted for or if v12 will be compatible. I'm
working in Compat Mode so I don't accidently grab a 2007 whistle or bell and
then have to remove it.

John G.