Solved: insert dates with userform

[zest1]

Can some please help me configure a userform to insert dates in cell A1?

I?d like for it to do the following - when the userform is opened:

1. display and highlight in the textbox the date (if any) that is in cell A1
2. take any new date typed in the textbox and insert it into cell A1 (format mm/dd/yyyy - allow spaces to act as "/", but do not allow text), or
3. use one of the 2 option buttons (for ?TODAY()? and Tomorrow) to insert one of those date)
4. OK button inserts the date into A1

Thanks for your help

[lucas]

Hi Zest,
Here is a start in the right direction I think..

[Charlize]

Try this one. But why not using the calendar control 8.0 ? On click on the calendar you put the value in a variable and when ok inserted in sheet.
You could use the worksheet beforedoubleclick event instead of drawing rectangles. Specify targetrow = 1 and targetcolumn = 1.

Charlize

[zest1]

Charlize,
thanks a lot!

That's exaclty what I was looking for. I added some code to highlight the textbox content (to save from having to click in the textbox to overwrite the contents).

I thought about using the calendar, but wanted to keep things simple - though I may want to use it, along with your beforedoubleclick event idea.

Can you give me a step-by-step on how to do it?

Thanks again for your help!

[Charlize]

After some digging in my collection I found what I was looking for. An example of the calendar control with userform. Maybe it will be usefull. Anyway, here it is.

Charlize

doubleclick event is on sheet basis. So in vbe doubleclick on sheet1 to open codesheet for sheet1. Then you can choose worksheet beforedoubleclick.
with
[vba]If target.column = 1 and target.row > 1
calendar_form.show
end if[/vba]
you say that with doubleclick in column 1 and row greater than 1 show the calendar.

[zest1]

Thanks a lot Charlize!

OK, I've integrated the doubleclick method and removed the rectangle, and I included the calendar in my original form - it is opened with a command button on the main form, only when needed. It works great.

One small issue though. When in calendar mode, selecting a date inserts that date directly into the target cell (A1). It should do nothing until after both 'OK' buttons (on the calendar and main forms) are clicked. The date from the calendar should be inserted into the text box, rather than the cell. And the Cancel button on the calendar form should allow cancelling out of the calendar, and going back to the main form without any changes to the date.

The code for the calendar 'OK' button needs to be changed (I just copied the same code from the other form). Can you tell me what changes are needed?

Thanks again for your help with this.

[Charlize]

Is it this. With the calendarform there's no need to do errorchecking because you can't select (i think) a date thats wrong formatted.

Charlize

[zest1]

PERFECT!

Thanks you so much Charlize!

You're a great help

[Petrogeo]

Excellent examples...

Petrogeo