Calendar Control in EXCEL 3

[bluebilly]

I want to add a calendar to an Excel spreadsheet and have successfully 'drawn' one having selected Calendar Control via the More Controls option from the Control Toolbox.
Basically, how do I now make the calendar clickable so that it stores a selected date in a cell ?
I have no VB knowledge to speak of so would appreciate an idiot's guide if possible !

Many Thanks

 

[DaleWatson123321]

bluebilly,

I have an example file I've created. I can email you this if you like. Email me and I'll send the file via return email.

Basically, it's a matter of setting up a VBA routine such as the following...

Private Sub Calendar1_Click()
If ActiveCell.Column <> 2 Then Exit Sub
ActiveCell.Value = Calendar1.Value
End Sub


Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[GlennUK]

In design mode, with the calendar selected, click the properties button, which will show the properties window, and for the property called LinkCell, type in a value of A1 or C4 or whatever cell reference you want the date stored in.

That's it.

Cheers, Glenn.

 

[PeterMoran]

Hi bluebilly,

Here is the address to Martin Green's VBA tips on creating an Excel popup calendar step by step:

http://www.fontstuff.com/vba/vbatut07.htm

It must be good! Even I got it to work!

Good Luck!

Peter Moran
Two heads are always better than one!!

 

[bluebilly]

Re GlennUK's response - this appears to be the simplest solution which even I understand - however when I 'draw' the calendar having selected it from the Control box - it is still not clickable even after I have completed the CellLink property.

Thanks for your time

 

[GlennUK]

Bluebilly,

after you've finished setting up the calendar ( and after setting the LinkedCell property), you need to simply end Design Mode. Click the first symbol of the Control Toolbar, and the calendar should start working.

Cheers, Glenn.

 

[bluebilly]

Thanks Glenn, that's great

Cheers

 

[bluebilly]

I've linked the calendar control to a cell in my worksheet (k25), but how do I permanently set k25 to a number format as opposed to a date format ?
Despite formatting k25 to Number format, everytime I select a new date using the calendar control, it returns say, 20/02/2003 instead of 3762 in cell k25, the latter format I require for sorting.

Any ideas ?

Thanks
Bill

 

[GlennUK]

Are you going to be sorting by date? If the cells you are going to be sorting on are true Excel dates then the sorting will work fine if the dates are formatted anyway.

Glenn.

 

[bluebilly]

Thanks Glenn, I've solve it by making an adjoining cell to k25 as =value(k25) and then using this adjoining cell to sort.
Cheers