VBA - Calendar with more than 1 textbox

[Eddie_SP]

Hi, now I have one UserForm with many textboxes and just one calendar.

How can I click on first textbox and click on calendar,
click on the second textbox and click on calendar, etc...

And after clicking on a CommandButton the date of each textbox goes to their
specific cells?

I'm having the problem of clicking on the CButton and having the last date
going to all cells...

Please help me.

 

[ryguy7272]

Did you try this?
http://www.rondebruin.nl/calendar.htm

There was a good example here:
http://www.fontstuff.com/

Site seems to be down now, but I've never seen it down before. Maybe it
will be active again very soon...

HTH,
Ryan---

 

[Eddie_SP]

Hi,

What I need is:

In the UserForm I have two textboxes (textbox1, textbox2), I have one
CommandButton and also I have only one calendar.

When I open the UserForm, I will click on the first textbox (just for
selection), and after click on the calendar selecting the first date.

After that, I will select the second textbox and click on the calendar
again, choosing the second date.

When I click on the CommandButton, the fisrt date will go to cell "A1" and
the other to "A2" for example.

But the problem I'm facing is that when I click on the calendar, the date
goes to both textboxes, because I'm using the comand below:



Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
TextBox1.Text = MonthView1.Value
End Sub



But it only works with 1 calendar and 1 textbox.

I`m trying to minimize my work, instead of making many UserForms for each
date, I will just make one UF for all dates.

I appreciate your help.

Eddie.

 

[Rick Rothstein]

Why not have the date placed in the TextBox immediately? Declare a
UserForm-wide global variable to track the last TextBox with focus...

Dim ActiveTBx As Object

Then put this code in the Enter event of each TextBox that will have a
calendar date assigned to it...

Set ActiveTBx = ActiveControl

And put this code in the Enter event of **every other** control...

Set ActiveTBx = Nothing

And put this code in the Click event of the Calendar control...

If Not ActiveTBx Is Nothing Then
ActiveTBx .Text = Calendar1.Value
Set ActiveTBx = Nothing
End If

Finally, put this code in the UserForm's Initialize event...

Set ActiveTBx = TextBox1
TextBox1.SetFocus

where you would replace the TextBox1 references with the name of the TextBox
that you want to have focus initially. This should now put the date in only
those TextBoxes that you set the ActiveTBx variable to the ActiveControl in
their Enter event procedures. Below is a sample for five TextBoxes (name
TextBox1, TextBox2, etc) where only the first four will be allowed to
receive dates from the Calendar control and where I have embellished the
display to show the TextBox's (the one that will get the date) BackGround
color set to yellow. To see this example in action, add another UserForm,
place five TextBoxes on it along with a Calendar control, then copy/paste
the below code into that new UserForm's Code window, and then hit the Run
button.

'*************** START OF CODE ***************
Dim ActiveTBx As Object

Private Sub Calendar1_Click()
If Not ActiveTBx Is Nothing Then
ActiveTBx.Text = Calendar1.Value
ActiveTBx.BackColor = vbWindowBackground
Set ActiveTBx = Nothing
End If
End Sub

Private Sub TextBox1_Enter()
If Not ActiveTBx Is Nothing Then ActiveTBx.BackColor = vbWindowBackground
Set ActiveTBx = ActiveControl
ActiveTBx.BackColor = vbYellow
End Sub

Private Sub TextBox2_Enter()
If Not ActiveTBx Is Nothing Then ActiveTBx.BackColor = vbWindowBackground
Set ActiveTBx = ActiveControl
ActiveTBx.BackColor = vbYellow
End Sub

Private Sub TextBox3_Enter()
If Not ActiveTBx Is Nothing Then ActiveTBx.BackColor = vbWindowBackground
Set ActiveTBx = ActiveControl
ActiveTBx.BackColor = vbYellow
End Sub

Private Sub TextBox4_Enter()
If Not ActiveTBx Is Nothing Then ActiveTBx.BackColor = vbWindowBackground
Set ActiveTBx = ActiveControl
ActiveTBx.BackColor = vbYellow
End Sub

Private Sub TextBox5_Enter()
Set ActiveTBx = Nothing
End Sub

Private Sub UserForm_Initialize()
Set ActiveTBx = TextBox1
TextBox1.SetFocus
End Sub
'*************** END OF CODE ***************

 

[Eddie_SP]

Hi Rick, I have uploaded the workbook, can you please take a look at it?

http://www.sendspace.com/file/pvygg2


I did not understand what you've meant. Sorry, I've just started working
with VBA.

Sorry.

 

[arjen van...]

A method I've used is to utilize the double click event of the textbox. The
following appears in the code for the user form (note: calDate is the name
given to the calendar control):

Private Sub txtDate1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

txtDate1.Text = calDate.Value

End Sub

Private Sub txtDate2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

txtDate2.Text = calDate.Value

End Sub

Then click the run button (something like this):

Private Sub btnRun_Click()

Call Module1.TestFile

End Sub

And then the following code appears in a general module (note:frmQuery is
the name of the user form):

Sub TestFile()

Dim tDate As String
tDate = Format(frmQuery.txtDate1.Text, "dd-mmm-yy")

Dim sDate As String
sDate = Format(frmQuery.txtDate2.Text, "dd-mmm-yy")

Unload frmQuery

With Sheets(2)
.Range("A1").Value = tDate
.Range("A2").Value = sDate
End With

End Sub

Seems to work for me.

 

[Rick Rothstein]

Oh, you are not using a Calendar Control... you are using a MonthView control (some of the properties are different on it than on the Calendar control). Save your code someplace so that you don't lose it and then replace all your code in the UserForm's code window with the code below (copy/paste it). To use the code, select a TextBox and then click a date on the MonthView control... you can go back to any TextBox and click the MonthView control to "correct" an erroneously entered date.

'*************** START OF CODE ***************
Dim ActiveTBx As Object

Private Sub CommandButton1_Enter()
Set ActiveTBx = Nothing
End Sub

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
If Not ActiveTBx Is Nothing Then
ActiveTBx.Text = MonthView1.Value
ActiveTBx.BackColor = vbWindowBackground
Set ActiveTBx = Nothing
End If
End Sub

Private Sub TextBox1_Enter()
If Not ActiveTBx Is Nothing Then ActiveTBx.BackColor = vbWindowBackground
Set ActiveTBx = ActiveControl
ActiveTBx.BackColor = vbYellow
End Sub

Private Sub TextBox2_Enter()
If Not ActiveTBx Is Nothing Then ActiveTBx.BackColor = vbWindowBackground
Set ActiveTBx = ActiveControl
ActiveTBx.BackColor = vbYellow
End Sub

Private Sub UserForm_Initialize()
Set ActiveTBx = TextBox1
TextBox1.SetFocus
End Sub
'*************** END OF CODE ***************

 

[Eddie_SP]

Rick, it did work !!! hehe

Thank yoooou ! And hey, I was not lazy. Just didn't understand. =)
And thank you everyone for helping me.

Love this community ! =)

 

[ryguy7272]

Rick, just one small modification:
Private Sub CommandButton1_Click()
Range("A1") = TESTE.TextBox1.Value
Range("A2") = TESTE.TextBox2.Value
Unload Me
End Sub

Anyway, that worked for me.
Ryan---

 

[Rick Rothstein]

And a good addition that is (I only just noticed the OP's request to put the results in cells on the worksheet in his response to you). Thanks for following up on that.