Pop up Calendar error

[Guest]

Hi there,

I've added calendar to an excel spreadsheet so that when the user clicks
certain cells the calendar pops up and lets them choose the date. I have a
problem and two questions.

Problem: as long as the worksheet is unprotected it works great but when I
lock other cells on the worksheet that have formulas then protect the sheet,
I get an error that says "Unable to set the NumberFormat property of the
range class" Does anyone know what this means and how I can get around it?

Questions: Is there a way to make the calendar disappear automatically once
a date has been chosen. Currently I have to choose a date then click on a
different cell which brings me to question two. The cells beside the date
column are merged and for some reason I cannot click out of the calendar on a
merged cell. Does anyone know a way around this?

Thanks

 

[Ron de Bruin]

Hi Klee

If you use this example then read the information on the page about protection
http://www.rondebruin.nl/calendar.htm

You can add
Calendar1.Visible = False


Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
'ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
Calendar1.Visible = False
End Sub

 

[Guest]

Thanks very much. It works great now. Man I love this website!

 

[Guest]

Hi Ron,
I hate to bug you again but I have a new problem now. My spreadsheet is
great but I sent it to someone to look at and they are getting an error
everytime they click a cell. The error says "Can't find project or library"
Do you know what this means?

Thanks

 

[Ron de Bruin]

Do you know what this means?

Yes, if you read the info on the page you see that not everybody have this control.
You can download it (see link on the bottom of the page)
Also you can have a problem with the version of the control in your workbook.

From Excel 2000 to Excel 2002 or 2003 OK
From Excel 2003 to excel 2000 it is not working

Develop in the oldest version

 

[Gord Dibben]

Klee

Someone probably doesn't have mscal.ocx installed on their computer or they need
to check the mscal.ocx in VBE Tools>References.

I suspect the former.

Ron provides a link to a download site.

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

 

[Guest]

Thanks, I had read that part but apparently not close enough, sorry. It seems
like the bottom line is that if I want to distribute this to my office I will
have to have everyone install the mscal file.

Thanks for all the help