pop up calendar using MS DateTime Picker

[andy62]

I looked over Ron Debruin's excellent site to create a pop-up calendar, but I
think having to get all my users to download the add-in would be problematic.
Then I noticed in a book* a description of Microsoft's "Date and Time Picker
Control", which seems to be available to everyone using 2003. But when I
tried to implement it, it seems like the pop-up - which is always visible
(not a problem) - can only be linked to one cell (a problem). Instead of
dropping the date into the "active" cell, it updates the one you have linked
to. And if you try to link to a range, e.g., D3:K8, it ignores everything
except the initial single cell referenced. Am I just doing something
wrong/does anyone know whether DateTime Picker can be used across multiple
cells?

TIA.

 

[Chip Pearson]

You can't link the control to more than one cell. You can, however, have
several cells linked to the control's LinkedCell range.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[andy62]

Thanks for the response, Chip. But that sounds exactly how I was trying to
link the control to a range of cells - through the LinkedCell setting in
Properties. Using D34 doen't work because it seems to ignore everything
after the colon. Neither does D3,D4 (which wouldn't help, anyway, with 700
cells in need of a calendar).

I guess I'm still not even clear if this can be done, because your two
sentences sound contradictory. Or I'm just tired. Thanks in advance if you
can clarify any way to have a single control place dates across a range of
cells (of course, I am not looking for it to place the same date across the
whole range).

 

[Chip Pearson]

Andy,

I meant a formula link, not a VBA link. If you link the control to one
cell, say Sheet1!C5, you can link to that cell from any number of other
cells with a formula like

=Sheet1!C5

This will put the value of the date picker into every cell that is linked to
Sheet1!C5. The net effect is that the value of the date picker is placed in
multiple cells.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[andy62]

Okay, thanks. So now I know why all the calendar questions are answered with
de Bruin's URL rather than the DateTime Picker!