How to Pass a date from a calendar control to a field on a form

[access user]

Hi

I have followed the link that RondeBruin gives to this site

http://www.fontstuff.com/vba/vbatut07.htm

which shows how to add a calendar control. However I want to pass the date
from the calendar to a field on a form (the calendar and field in question
are both on the same form). The info on the above link only shows how to pass
it to an active cell on the worksheet.

Would appreciate any help.

tia
James

 

[Dave Peterson]

Maybe something like:

Me.label1.caption = format(calendar1.value,"mmmm dd, yyyy")

 

[access user]

Hi

Thanks for your response, however it does not work. I have the following code:

Private Sub EnterDate()
'Me.txtPeriodFrom.ControlSource =
Worksheets("InputDataEntry").Range("A1").Value
Me.txtPeriodFrom.ControlSource = Format(Calendar1.Value, "mmmm dd, yyyy")
End Sub

Where I have "ControlSource" is where you had "caption" in your suggestion.
Caption was not recognised.

Any other suggestions?

tia
James

 

[access user]

in my code please ignore the first section as it is headed with ' (I was just
trying that out). So the code I am using and that does not work is:

Me.txtPeriodFrom.ControlSource = Format(Calendar1.Value, "mmmm dd, yyyy")

tia
James

 

[Patrick Molloy]

assuming that the objects are on teh same form, then you don't really need
ther "me" thing

Private Sub Calendar1_Click()
Label1.Caption = Format$(Calendar1.Value, "ddd mm yyyy")
End Sub

 

[Dave Peterson]

Is txtPeriodFrom a textbox on that same form?

Me.txtPeriodFrom.Value = Format(Calendar1.Value, "mmmm dd, yyyy")

I still like to qualify my objects.

If this doesn't help, it's time to share more details.

What is txtPeriodFrom?
Is the calendar control part of the same userform?

 

[access user]

Hi

Thanks - that works - but how about if I want three dates? So when the focus
is on the date1 textbox and I click the calendar, I want the date put in that
box, when it is on date2 textbox.....etc

I am guessing I would need to change the "Label1.Caption= " to a variable
which changes value according to whichever box is in focus?

can you help with that?

tia
James

 

[access user]

Hi

Yes the three text boxes for the dates are on the same form as the calendar.
Your code below works.

However, I think you replied just as I posed my additional question. So now,
if I want the date to go into whichever box has currently got the focus then
how would you do that?

tia
James

 

[Dave Peterson]

Won't the calendar control have focus?

But maybe you could keep track of which textbox you were in last:

Option Explicit
Dim LastTextBox As MSForms.TextBox
Private Sub TextBox1_Enter()
Set LastTextBox = Me.TextBox1
End Sub
Private Sub TextBox2_Enter()
Set LastTextBox = Me.TextBox2
End Sub
Private Sub TextBox3_Enter()
Set LastTextBox = Me.TextBox3
End Sub

=====
Then in whatever procedure you're getting the dates...

lasttextbox.Value = Format(Calendar1.Value, "mmmm dd, yyyy")

But it would probably be better to check to see if there is a lasttextbox before
you ask for the date:

If LastTextBox Is Nothing Then
MsgBox "Select a textbox first!
exit sub 'or something????
end if

Hi

Yes the three text boxes for the dates are on the same form as the calendar.
Your code below works.

However, I think you replied just as I posed my additional question. So now,
if I want the date to go into whichever box has currently got the focus then
how would you do that?

tia
James

 

[access user]

Ok - now I get the following error

error 91
Object variable or With block variable not set

tia
James

 

[Dave Peterson]

On what line?

Remember that if you never entered a textbox--or didn't change your code to keep
track, then lasttextbox would be nothing.

If LastTextBox Is Nothing Then
MsgBox "Select a textbox first!
exit sub 'or something????
else
lasttextbox.Value = Format(Calendar1.Value, "mmmm dd, yyyy")
end if

Ok - now I get the following error

error 91
Object variable or With block variable not set

tia
James

 

[access user]

On the line
lasttextbox.Value = Format(Calendar1.Value, "mmmm dd, yyyy")

Anyway, I have now incorporated your check to see if a box was selected,
however, even when I select a box I always get the error prompt "select a
textbox first". Isn't the problem that as soon as the calendar is clicked the
focus from the textbox is lost? Would it help if the calendar was on its own
separate form? ie - not on the same form as the textboxes?

For completeness below is the full code as I have it now:

Option Explicit
Dim LastTextBox As MSForms.TextBox

(above is under General Declarations)
___________________________________
Private Sub TextBox1_Enter()
Set LastTextBox = Me.txtPeriodTo
End Sub

(above is under General)
__________________________________
Private Sub TextBox2_Enter()
Set LastTextBox = Me.txtPeriodFrom
End Sub

(above is under General)
___________________________________
Private Sub TextBox3_Enter()
Set LastTextBox = Me.txtDate
End Sub

(above is under General)
____________________________________
Private Sub Calendar1_Click()
If LastTextBox Is Nothing Then
MsgBox "Select a textbox first!"
'Exit Sub 'or something????
Else
LastTextBox.Value = Format(Calendar1.Value, "mmmm dd, yyyy")
End If
End Sub

(above block is under Calendar1 Click event)
_________________________________

 

[Dave Peterson]

The events will be based on the name of the textboxes.

You'll need to make changes like this:

Private Sub txtPeriodTo_Enter()
Set LastTextBox = Me.txtPeriodTo
End Sub

For all of the textboxes.

On the line
lasttextbox.Value = Format(Calendar1.Value, "mmmm dd, yyyy")

Anyway, I have now incorporated your check to see if a box was selected,
however, even when I select a box I always get the error prompt "select a
textbox first". Isn't the problem that as soon as the calendar is clicked the
focus from the textbox is lost? Would it help if the calendar was on its own
separate form? ie - not on the same form as the textboxes?

For completeness below is the full code as I have it now:

Option Explicit
Dim LastTextBox As MSForms.TextBox

(above is under General Declarations)
___________________________________
Private Sub TextBox1_Enter()
Set LastTextBox = Me.txtPeriodTo
End Sub

(above is under General)
__________________________________
Private Sub TextBox2_Enter()
Set LastTextBox = Me.txtPeriodFrom
End Sub

(above is under General)
___________________________________
Private Sub TextBox3_Enter()
Set LastTextBox = Me.txtDate
End Sub

(above is under General)
____________________________________
Private Sub Calendar1_Click()
If LastTextBox Is Nothing Then
MsgBox "Select a textbox first!"
'Exit Sub 'or something????
Else
LastTextBox.Value = Format(Calendar1.Value, "mmmm dd, yyyy")
End If
End Sub

(above block is under Calendar1 Click event)
_________________________________

 

[access user]

Doh - of course! Sorry for being so silly. It all works now. Thanks very much
for your help.

James

 

[Dave Peterson]

I don't like typing these event procedure names. I figure that all I can do is
screw them up.

Instead, I use the topleft dropdown choose the correct control/category and the
topright dropdown to choose the correct event.

Doh - of course! Sorry for being so silly. It all works now. Thanks very much
for your help.

James

 

[access user]

Thanks for that additional tip - makes more sense now.
James