XL 2003 Calendar Control Problem

[JeffT]

Hi

I'm trying to get the activex MSCal control to work in a simple form I'm making for work. I run WinXP and Xl2003 work has W2K & Xl 2002 I think.

I want to be able to bring up a calendar, click in a cell, click on the date in the calendar so it enters the cell, then click in a different cell, click the calendar to enter the date etc. etc.

I can open the Calendar as an embeded object by using a macro (I don't need it displayed all the time), but then the only way I can get it to work is to click the VBA Design mode button on the toolbar on and off. I don't know how to replicate this with code (or why it needs to be done). Once this is done, it works fine.

I then tried bringing up a dialog box with the calender control in. This works the first time you click the date, but it won't let me go to a different cell without shutting down the dialog box then reopening it once I've moved.

I also tried opening the embeded version when the sheet opened though it gets in the way for most of its uses. However even though I've set Format Object / Properties to "Don't move or size with cells", it does move when I scroll, and because I have some panes frozen it half dissapears I can' get it small enough to be usableif it sits in the few frozen cells. I even tried setting it to "Icon" though this didn't seem to do anything (may give that another go). Is this just a bug? I'm using the Calendar control 10 as I didn't get it with my Version of Office.

Hope someone can help

Regards & thanks for the help in the past

Jeff T

[Killian]

Hi Jeff

I don't know the calender control you're using, but I've put together something that uses the "Mircosoft Date and Time Picker Control Version 6.0" that comes with XL XP/2003
Insert one onto your worksheet in design mode and set it's visible property to False. (it's name should already be DTPicker1)
The code below uses the worksheet right click event to create a "Select date" menu item. The CloseUp event of the picker sets the cell value and hides the picker again.
This code should go behind the sheet that contains the date picker

'CREATES A RIGHT CLICK MENU ITEM AT THE TOP OF THE MENU TO SELECT THE DATE


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim mnuDate As CommandBarControl
'delete old right click menu items
For Each mnuDate In Application.CommandBars("cell").Controls
If mnuDate.Caption = "Select date..." Then mnuDate.Delete
Next
'add right click menu item
Set mnuDate = Application.CommandBars("cell").Controls.Add(temporary:=True)
With mnuDate
.Caption = "Select date..."
.Move Before:=1
.OnAction = "ShowDatePicker"
End With
End Sub

'THE DATEPICKER EVENT THAT FIRES WHEN THE CALENDER CLOSES AFTER A DATE IS SELECTED


Private Sub DTPicker1_CloseUp()
ActiveCell.Value = DTPicker1.Value
DTPicker1.Visible = False
End Sub

The next bit of code shows/positions the picker and should go in a seperate module

' ROUTINE TO DISPLAY THE DATEPICKER OVER THE ACTIVE CELL


Sub ShowDatePicker()
With ActiveSheet.DTPicker1
.Visible = True
.Locked = False
.Left = ActiveCell.Left
.Top = ActiveCell.Top
End With
End Sub

[JeffT]

Hi Killian

I don't appear to have any control named "Mircosoft Date and Time Picker Control Version 6.0" or anything at all like that.

I don't have Access, does it get loaded when you install that? It seems the "Calendar Control 10.0" (or I believe it's version 11.0 now) comes with Access. I've copied it from a computer at work hence use version 10.0 not 11.

It's an ActiveX control loaded from "More Controls" in the "Control Toolbox" on the VB Toolbar.

Regards

Jeff T

[Killian]

Hi Jeff

Well I suppose we should expect this kind of thing...
It's part of my Office install (XP, no Access). The control itself is in file MSCOMCT2.OCX, listed in the references dialog in the VBEdtor as "Microsoft Windows Common Controls-2 6.0".
It's quite possible that although we both have XP, we don't have the same controls or the controls we do have are different versions and aren't completely compatible. It depends on which service packs are installed, other apps that use Activex controls and probably the phase of the moon.
It might be worth posting an example workbook and the control (zip them up together) to keep it simple...

K

[johnske]

Hi JeffT,

Does the attachment do the sort of thing you mean? I'm using 2000 and it's "Calendar Control 9.0" in there. I'd be very interested to know if the GUID reference is still the same though (it should be).

Regards,
John


EDIT: Just discovered you also need a reference to Microsoft Common Controls as well (added this into the attachment and re-attached)

[JeffT]

johnske

I tried opening your Calendar2 but no calendar appeared. Looking at the VB code you have 2 items like

.......AddFromGuid _
"{0D452EE1-E08F-101A-852E-02608C4D0BB4}

These look like the kind of folders under .... Identities on my computer. However I did a search on C: and found I don't have any folders or files named like the 2 in your example. Perhaps I'm on completely the wrong track but being self taught don't always understand the terminology. I just use the lemon test till something works.

I've tried adding my ActiveX MSCal control to your dialog box and although it opens with a calendar nothing happens when you click a date. Yours opened but with no calendar.

Ahh... Just added the following code, which I copied from my example


Private Sub Calendar1_Click()
'This inserts the date into the selected cell
'when the day button is pressed.
ActiveCell = Me.Calendar1
End Sub

And it works. I'll have to study this to see what code I can remove and why this works when it didn't before. Thanks

I'd worked out a way of doing it myself, but that was using 6pt typeface on the calendar and having it embeded always open and squeezing it into the top left corner of the freezepanes area which never moves. However yours is exactly what I wanted to do so thanks


Regards

Jeff T

[johnske]

Hi Jeff,

Not a prob, glad to see you sorted it out... The AddFromGuid simply adds the required references if they're not already there, you can also add them by hand (which you appear to've done)

Regards,
John
:beerchug:

[geekgirlau]

Killian, love the code :clap: - have you submitted this as a KB yet?

[Killian]

thnx. It is kinda groovy... if you have the control...
as for the kb, my list of potential submissions is growing at an alarming rate inside my head but I have to work on my typing speed (and attantion span) :whistle: