Better calendar control

[bhardage]

I'm trying to make it so that when a user clicks on a certain cell, a calendar pops up so they can pick a date. This seems like a pretty common thing to want; I hope Microsoft puts it into the next release.

Anyway, I've read the tutorial found at http://www.fontstuff.com/vba/vbatut07.htm which tells you how to create a userform, add the calendar control to it, etc.

I've also modified it so that the calendar comes up when the user clicks on a certain cell, and so that the form closes when they choose a date.

I had to use the selection_changed subroutine to tell if someone clicked on the cell, but there are some flaws.

First, moving over to the cell with the keyboard arrows brings up the calendar (undesired result; I only want it to come up with clicking)

Second, if the cell is already selected, clicking it doesn't bring up the calendar since the selection didn't change (also undesired; I would like the form to come up whether the cell was previously selected or not).

Does anyone have any suggestions? Or maybe an alternative way to go about it?

Thanks,
B.J.

[royUK]

See this - http://www.excelforum.com/developmen...ndar-form.html

Or see the attached

[bhardage]

That calendar doesn't actually solve my problems. It's not the look and feel of the calendar control that I'm having trouble with. Please reread my problem and you'll see what I'm saying.

I don't want to use a form button to open it. I want it to open when clicking on a cell.

Thanks for trying anyway.

B.J.

[shg]

Please reread my problem and you'll see what I'm saying.

People here routinely provide solutions based on what has served other applications. Roy could scarcely have answered your question had he not read your post. If you want custom development to your specifications, consider the commercial services forum.

I don't want to use a form button to open it.

We tend to assume to people are capable of adapting related solutions to their current problem, absent a disclaimer. Roy's calendar could readily be adpted to pop up when a cell with date formatting is selected.

First, moving over to the cell with the keyboard arrows brings up the calendar (undesired result; I only want it to come up with clicking)

The selection change event can't distinguish how you arrived; Window API may be able to.

[royUK]

The calendar post has several adaptations in it. The major problem you will find with the calendar control is that some users will not have the ocx installed, therefore it won't work on their computers..

Second, if the cell is already selected, clicking it doesn't bring up the calendar since the selection didn't change (also undesired; I would like the form to come up whether the cell was previously selected or not).

What would tell Excel you need the calendar displayed- there would be no event occurring. You could use the worksheet_activate to check if a cell contains a date, or check the NumberFormat but this would only occur when the sheet is activated.

[bhardage]

My intention was not to offend anyone.

I'm simply frustrated by the fact that excel does not contain any sort of built-in drop-down calendar control, and I feel somewhat helpless to make my own because of the lack of any onClick event.

[royUK]

My intention was not to offend anyone.

I'm simply frustrated by the fact that excel does not contain any sort of built-in drop-down calendar control, and I feel somewhat helpless to make my own because of the lack of any onClick event.

I'm not sure what you mean, Excel can use either of the Datepicker or Calendar controls, unfortunately they can be problematical that's why I posted the calendar form example which because it is unprotected allows you to adapt i's code for your use.

There is nt really any way to trigger the form if the cell is already selected, you need to actually select the cell

[reggie1000]

Is this what your looking for?

thanks reg

[royUK]

Is this what your looking for?

thanks reg

That example is very similar to what I have already suggested.

[bhardage]

I guess ideally what I'm looking for is something exactly like the drop-down list data-validation, in that when you click the cell a little arrow pops up beside it and when you click the arrow a list of values to choose from pops up.

Ideally my control would be exactly like that, except clicking on the little arrow would cause a calendar to pop up below the selected cell instead of a list of values.

I realize that this kind of thing probably isn't possible, and I will have to deal with what is possible, i.e. the calendars you've provided, or a userform with the built-in calendar control on it.

Thanks,
B.J.

[royUK]

have you tried the DatePicker addin from

http://www.excel-it.com/free_addins.htm

[bhardage]

well, I found something like what I was looking for here: http://www.vista-files.org/programs/...-calendar.html

Unfortunately it's not free, and I'm not sure how my customer would feel about buying the license.

This brings up a question in my mind though; How does one go about writing a plug-in for excel?

[royUK]

It's probably a com addin. The DatePicer addin is similar. I'll review it & maybe add some features when I get time.