Calander object in Userform (2003 SP2)

Topic

New Reply

Good afternoon

On a user form that has date entry fields I have placed a calander to try and avoid data entry errors, it opens fine but when I double click a date it does not update the field on the user form, I presume that I must somehow call the calander into the user form (or date field) with code but I am either ‘googling’ the wrong question entirely or I am missing the point altogether.

Can any body advise please how I get it to interact with the date field?

Cheers

Steve

Reply | Quote

Replies

Steve

I had a similar problem, and I got round it by creating a separate form with the calendar object on it. I used the Click event on various command buttons with a calendar image on them to open the calendar form modally. The code also sets a global control variable according to which date control has to receive the date. There is an OK command button on the calendar form which sets the date on the global control variable, and then closes the calendar form.

I found this to much easier than having several calendar controls on the user form itself. Let me know if you need any more details.

Nick

Reply | Quote

Thanks Nick

Please remember I am a complete and utter novice at this

That’s too many technicalities in one post for me and I am afraid to admit it did not mean much!!

I have added this to the code in my user form which I have adapted from a form I found on the net and by jingo it worked, unfortunately though it shows the American date, i.e. the 12th of April instead of the 4th of December (12/4/2007 instead of 04/12/2007)

Private Sub Calendar1_Click()
txtDateFrom= Calendar1.Value
Me.Calendar1.Value = txtDateFrom
End Sub

I shall go and play with dd/mm/yy and see if I can get it to work.

Cheers

Steve

Reply | Quote

Why are you setting the value of the calendar (Me.Calendar1.Value = txtDateFrom)? That’s superfluous.

Reply | Quote

Hi Hans

Sorry but my posts are crossing.

As mentioned to Nick I got the code from a userform on the net but have now changed it to your example as you suggest

Thanks as normal

Steve

Reply | Quote

You can write an event procedure for the On Click event of the calendar control:

Private Sub Calendar1_Click()
Me.TextBox1 = Me.Calendar1
End Sub

or if you prefer to use the On Double Click event:

Private Sub Calendar1_DblClick()
Me.TextBox1 = Me.Calendar1
End Sub

Calendar1 is the name of the calendar control, and TextBox1 the name of the text box. You must replace them with the actual names on your userform.

Reply | Quote

Thanks Hans

You got in while I was composing, can you advise how I change the dates around from mm/dd/yy to dd/mm/yy?

Thanks

Steve

Reply | Quote

Make sure that your Windows is set to use UK date format.

Reply | Quote

Sorry, please ignore that it is now sorted, however

I have 2 date fields on my user form but I cannot get them both to update, I tried

Private Sub Calendar1_Click()
Me.txtDateFrom = Calendar1.Value
Me.Calendar1.Value = txtDateFrom
Me.txtDateTo= Calendar1.Value
Me.Calendar1.Value=txtDateTo
End Sub

But all that did was (a) enter the selected date into DateFrom and then Chaged DateFrom to what I was tring to put into DateTo

Then I tried

Private Sub Calendar1_Click()
Me.txtDateFrom = Calendar1.Value
Me.Calendar1.Value = txtDateFrom
End Sub
——————————————————-
Private Sub Calendar1_Click()
Me.txtDateTo = Calendar1.Value
Me.Calendar1.Value = txtDateTo
End Sub

But that bugged out, I thought because I had used Calendar1 in both arguements so I changed it to calendar2 for the DateTo but that did not work either

Pointers please

Cheers

Steve

Reply | Quote

You cannot create two procedures of the same name.

Please try to explain clearly what you are trying to accomplish. If you want to set txtDateFrom, all you need is

Private Sub Calendar1_Click()
Me.txtDateFrom = Me.Calendar1
End Sub

Reply | Quote

Hi Hans

On my UserForm I have 2 date fields txtDateFrom and txtDateTo I wanted to use the calander control to enter the dates into them, doing it with the txtDateFrom works well as per your example but I am tring to use it to also populate txtDateTo

Thanks

Steve

Reply | Quote

Do you want to set them to the same date?

If so, you can add a line to the On Click event procedure:

Private Sub Calendar1_Click()
Me.txtDateFrom = Me.Calendar1
Me.txtDateto = Me.Calendar1
End Sub

If not, you shouldn’t use the On Click event of the calendar control. Place two command buttons next to (or below) the calendar control:

1) A command button cmdSetFrom with caption Set From Date and code

Private Sub cmdSetFrom_Click()
Me.txtDateFrom = Me.Calendar1
End Sub

2) A command button cmdSetTo with caption Set To Date and code

Private Sub cmdSetTo_Click()
Me.txtDateTo = Me.Calendar1
End Sub

Reply | Quote

Thank you Hans

I will approach it via the cmdButton route

Thanks again

Steve

Reply | Quote

As expected, worked like a charm

Cheers

Steve

Reply | Quote