date picker

[alcorjr]

Hi, on some programs I have seen, that when a date must be selected, you can do it through a button that opens uo a little calendar page on which you just click on the date selected.

Is there anything similar in Excel Office XP, or is this asking too much??

Thankyou all.

 

[pfarmer]

Re: date picker- where?, where?

Paddy, thanks for your suggestion. I'm eager to try it, but:
which toolbar are we talking about, the one on the sheet proper or the one on the userform?

what is the exact name of the additional "calendar control" you mention, cause I can't find it.


Thanks

Unfortunately I personally tried to use the calendar control, worked great at home. At home I have 2003 and at work where I needed this it is 2002.

The calendar control was not available on those 2002 machines so I ended up using DTPicker instead which does provide a drop down calendar.

Perry

 

[SHEETMAGIC]

THanks -- it works

Changing it to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H9")) Is Nothing Then
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub


Has made it work just great.

Thanks for that...

J

 

[SHEETMAGIC]

Re: date picker- where?, where?

The calendar control was not available on those 2002 machines so I ended up using DTPicker instead which does provide a drop down calendar.

Perry

I'd like to check out this DateTime Picker control.
When I go INSERT > OBJECT then that control does not appear in the list. The CalendarControl does, and this is how I usually insert it.

If I go to the MORE CONTROLS button on the Controls Toolbar then there is a huge list of controls and DTP is in there. If I select that I am then able to draw a box into the worksheet (presumably the location where I want the control to appear?) and then once I have done that I get the error "Cannot insert object".

Does anyone know why that error might occur, given the above description of the circumstances?

Thanks,

Jonathan

 

[SHEETMAGIC]

I am getting an odd problem now that I have the CalendarControl activated on this worksheet.

I have a range of cells that basically draw up seven days of the week, with the last date coming from the cell controlled by the CalendarControl. Something like this...

H9 = 22/09/2005 (Controlled by CC)

        -A-           -B-
14    Monday     16/09/2005
15    Tuesday    17/09/2005
etc
20    Sunday      22/09/2005

You might notice that these days are not correct (ie. 22/09/2005 is not a Sunday. It is a Thursday).

What is happening is this... once I have implimented that CalControl then something about the formating of H9 gets locked. I can't change the date formating at all. It will stay as dd/mm/yyyy no matter what I set it to.
Then, because B20 takes its value from H9 it too gets locked into this fixed formating.
Then because A20 is simply B20 with the date formating dddd it too gets locked and does not show it in that format. Rather it will show it the same way as H9.
I used to just have " =DAY(B20) " in A20 but that does not work once I use the CalControl because it seems to think that B20 has no year or month data. So it thinks that it is working with the value "22/01/1900" and thus makes the day incorrect. That was when I switched it to just being =B20 and then formating it as dddd. But that then doesn't work because of this weird locking that goes on.

I hope this makes sense.

Does any one know how I can get around this?

Thanks...
J

 

[erik.van.geit]

some two years ago I inserted this control into a project for hundreds of users
the day after I deleted it: to much trouble I got a mass of calls from users who where getting errors ...
still don't know why

 

[pfarmer]

Re: date picker- where?, where?

The calendar control was not available on those 2002 machines so I ended up using DTPicker instead which does provide a drop down calendar.

Perry

I'd like to check out this DateTime Picker control.
When I go INSERT > OBJECT then that control does not appear in the list. The CalendarControl does, and this is how I usually insert it.

If I go to the MORE CONTROLS button on the Controls Toolbar then there is a huge list of controls and DTP is in there. If I select that I am then able to draw a box into the worksheet (presumably the location where I want the control to appear?) and then once I have done that I get the error "Cannot insert object".

Does anyone know why that error might occur, given the above description of the circumstances?

Thanks,

Jonathan

Not sure why you get the error. However after reading this more I see you want a Calendar control on a Worksheet, not a Userform. In this case you may well run into a problem if you use DTPicker. That problem has to do with the fact that DTPicker doesn't like to maintain its size when used on a Worksheet..

Perry

 

[SHEETMAGIC]

Oh well...

Thanks Eric and Perry,

What I understand is that DTPicker is not the way to go on a worksheet.
AND that the CalendarControl can cause odd issues that may be more trouble than it's worth.
Bummer... It makes it much smoother using this control. Currently I have to open up a calendar elsewhere and then suss out what date I need to put into the cell at hand. Sloppy I thought... especially when there's a nice little calendar that can pop as as needed. But... it's screwing with my date formats.

I'll start a new thread asking if anyone knows of a way around this issue.

Thanks for your help guys.


Jonathan

 

[SHEETMAGIC]

Found a useful solution !!

Just to let others reading this thread know that I found a useful solution to this.

Check out http://www.fontstuff.com/vba/vbatut07.htm
There is a great little add-in that lets me right click a cell and select INSERT DATE. A little pop-up calendar appears and it sticks the date in without messing up the formating etc.

There is also instructions on how to simply add this code into a sheet so that no add-in is required etc.

Regards,

Jonathan

 

[Cusuma]

The size of the calendar is not kept?

When others are using my workbook and I open it again the calendar is not the same size. Why is that?

 

[erik.van.geit]

Hi, Cusuma,

This is an old thread and it is not sure who will notice your post. I suggest you started a new thread. You can always refer to here.
(I do not know the answer. )

kind regards,
Erik