I've got my form with TextBoxes and I've got my calendar.
I've even got the thing loading all the necessary fields into the right
cells in a new correctly formatted row on my worksheet.
All of this thanks to posts I found here - I've learnt so much in the last 2
weeks!

Three of the textboxes on this form need to take dates. How can I tell the
Calendar which of the three to send the data to ?
My solution:
Three separate calendars (and CommandButtons to send the Value) that only
become visible when the cursor enters the relative textbox. i.e. Calendar1
appears when entering TextBox1 then disappears after its button is clicked,
Calendar2 appears later on when entering TextBox2 etc. I could place them all
in the same position so to the user it'd look like the same calendar
appearing each time. I certainly don't want three calendars filling my form!

Is there a more direct way ? Some property of textBoxes like "HasFocus" or
"IsActive" that I could use ?

David,
The userform has an ActiveControl property, but in your case, this would be
the calendar not the text box.
You could give yourself a private object the track the destination textbox,
setting it when each tb is entered. Then the calendar (I've used a listbox
here, but the same applies) sends it's value to that tb:

Dim mtb As msforms.TextBox

Private Sub ListBox1_Click()
mtb.Value = ListBox1.Value
End Sub

Private Sub TextBox1_Enter()
Set mtb = TextBox1
End Sub

Private Sub TextBox2_Enter()
Set mtb = TextBox2
End Sub

NickHK

"David Macdonald" wrote in
message ...
I've got my form with TextBoxes and I've got my calendar.
I've even got the thing loading all the necessary fields into the right
cells in a new correctly formatted row on my worksheet.
All of this thanks to posts I found here - I've learnt so much in the last
2
weeks!

Three of the textboxes on this form need to take dates. How can I tell the
Calendar which of the three to send the data to ?
My solution:
Three separate calendars (and CommandButtons to send the Value) that only
become visible when the cursor enters the relative textbox. i.e. Calendar1
appears when entering TextBox1 then disappears after its button is
clicked,
Calendar2 appears later on when entering TextBox2 etc. I could place them
all
in the same position so to the user it'd look like the same calendar
appearing each time. I certainly don't want three calendars filling my
form!

Is there a more direct way ? Some property of textBoxes like "HasFocus" or
"IsActive" that I could use ?

Nick,
A million thanks! This also gets rid of the button too.
There was only one advantage I could see to my method - after putting the
date into the right textbox the cursor would go to the next field (as set up
in tab order) but now it always goes to the beginning of the form. There's
something for me to work on this morning...


"NickHK" wrote:

David,
The userform has an ActiveControl property, but in your case, this would be
the calendar not the text box.
You could give yourself a private object the track the destination textbox,
setting it when each tb is entered. Then the calendar (I've used a listbox
here, but the same applies) sends it's value to that tb:

Dim mtb As msforms.TextBox

Private Sub ListBox1_Click()
mtb.Value = ListBox1.Value
End Sub

Private Sub TextBox1_Enter()
Set mtb = TextBox1
End Sub

Private Sub TextBox2_Enter()
Set mtb = TextBox2
End Sub

NickHK

"David Macdonald" wrote in
message ...
I've got my form with TextBoxes and I've got my calendar.
I've even got the thing loading all the necessary fields into the right
cells in a new correctly formatted row on my worksheet.
All of this thanks to posts I found here - I've learnt so much in the last
2
weeks!

Three of the textboxes on this form need to take dates. How can I tell the
Calendar which of the three to send the data to ?
My solution:
Three separate calendars (and CommandButtons to send the Value) that only
become visible when the cursor enters the relative textbox. i.e. Calendar1
appears when entering TextBox1 then disappears after its button is
clicked,
Calendar2 appears later on when entering TextBox2 etc. I could place them
all
in the same position so to the user it'd look like the same calendar
appearing each time. I certainly don't want three calendars filling my
form!

Is there a more direct way ? Some property of textBoxes like "HasFocus" or
"IsActive" that I could use ?

David,
Not sure what you mean by "it always goes to the beginning of the form"...
But you could have another private variable to indicate the next textbox
that should have the focus.
Or save the name of the next textbox in the current textbox's .tag property,
then

Private Sub ListBox1_Click()
With mtb
.Value = ListBox1.Value
Me.Controls(.Tag).SetFocus
End with
End Sub


(Unfortunately VBA does not support Control arrays, which would tasks like
this more simple.)

NickHK

"David Macdonald" wrote in
message ...
Nick,
A million thanks! This also gets rid of the button too.
There was only one advantage I could see to my method - after putting the
date into the right textbox the cursor would go to the next field (as set
up
in tab order) but now it always goes to the beginning of the form. There's
something for me to work on this morning...


"NickHK" wrote:

David,
The userform has an ActiveControl property, but in your case, this would
be
the calendar not the text box.
You could give yourself a private object the track the destination
textbox,
setting it when each tb is entered. Then the calendar (I've used a
listbox
here, but the same applies) sends it's value to that tb:

Dim mtb As msforms.TextBox

Private Sub ListBox1_Click()
mtb.Value = ListBox1.Value
End Sub

Private Sub TextBox1_Enter()
Set mtb = TextBox1
End Sub

Private Sub TextBox2_Enter()
Set mtb = TextBox2
End Sub

NickHK

"David Macdonald" wrote in
message ...
I've got my form with TextBoxes and I've got my calendar.
I've even got the thing loading all the necessary fields into the
right
cells in a new correctly formatted row on my worksheet.
All of this thanks to posts I found here - I've learnt so much in the
last
2
weeks!

Three of the textboxes on this form need to take dates. How can I tell
the
Calendar which of the three to send the data to ?
My solution:
Three separate calendars (and CommandButtons to send the Value) that
only
become visible when the cursor enters the relative textbox. i.e.
Calendar1
appears when entering TextBox1 then disappears after its button is
clicked,
Calendar2 appears later on when entering TextBox2 etc. I could place
them
all
in the same position so to the user it'd look like the same calendar
appearing each time. I certainly don't want three calendars filling my
form!

Is there a more direct way ? Some property of textBoxes like
"HasFocus" or
"IsActive" that I could use ?

The calendar had ended up at the bottom of the tab list when I renamed it.
Now I have it just after the first textbox so once the date has been sent the
cursor goes to the next control. The problem is caused by there being more
controls than just the three textboxes where I need to input dates, and these
other controls come between one box of dates (xmas round the corner) and the
next.
So after getting to the second textbox and clicking the calendar, the date
is correctly input but the cursor returns to the same place as before (right
after textbox1) - it's following the tab order and one control can't be in
two positions in the tab list of course.
I figured it must be something with setfocus and probably an if/else
condition...


"NickHK" wrote:

David,
Not sure what you mean by "it always goes to the beginning of the form"...
But you could have another private variable to indicate the next textbox
that should have the focus.
Or save the name of the next textbox in the current textbox's .tag property,
then

Private Sub ListBox1_Click()
With mtb
.Value = ListBox1.Value
Me.Controls(.Tag).SetFocus
End with
End Sub


(Unfortunately VBA does not support Control arrays, which would tasks like
this more simple.)

NickHK

"David Macdonald" wrote in
message ...
Nick,
A million thanks! This also gets rid of the button too.
There was only one advantage I could see to my method - after putting the
date into the right textbox the cursor would go to the next field (as set
up
in tab order) but now it always goes to the beginning of the form. There's
something for me to work on this morning...


"NickHK" wrote:

David,
The userform has an ActiveControl property, but in your case, this would
be
the calendar not the text box.
You could give yourself a private object the track the destination
textbox,
setting it when each tb is entered. Then the calendar (I've used a
listbox
here, but the same applies) sends it's value to that tb:

Dim mtb As msforms.TextBox

Private Sub ListBox1_Click()
mtb.Value = ListBox1.Value
End Sub

Private Sub TextBox1_Enter()
Set mtb = TextBox1
End Sub

Private Sub TextBox2_Enter()
Set mtb = TextBox2
End Sub

NickHK

"David Macdonald" wrote in
message ...
I've got my form with TextBoxes and I've got my calendar.
I've even got the thing loading all the necessary fields into the
right
cells in a new correctly formatted row on my worksheet.
All of this thanks to posts I found here - I've learnt so much in the
last
2
weeks!

Three of the textboxes on this form need to take dates. How can I tell
the
Calendar which of the three to send the data to ?
My solution:
Three separate calendars (and CommandButtons to send the Value) that
only
become visible when the cursor enters the relative textbox. i.e.
Calendar1
appears when entering TextBox1 then disappears after its button is
clicked,
Calendar2 appears later on when entering TextBox2 etc. I could place
them
all
in the same position so to the user it'd look like the same calendar
appearing each time. I certainly don't want three calendars filling my
form!

Is there a more direct way ? Some property of textBoxes like
"HasFocus" or
"IsActive" that I could use ?