Open a form from a form VBA, Excel 2010

Topic

New Reply

I have a form that requires the input of a date into a text box and I would like to have a calendar pop up (another form) for the user to select the date. I have both forms but the calender closes as soon it has been initialized and the program continues back to the first form.

Is there a way to pause the calendar until a date has been selected?

Reply | Quote

Replies

Can you show us the code you have used to spawn the calendar control?

If that is the only thing on the second form, I would put it on the first form and set it as hidden. When you need to call it, just make it visible and hide it again once the selection is made.

Reply | Quote

Mary,

Here is a copy and paste of a response I gave to a similar situation. Extract what might be of some use and compare the code.

HTH,
Maud

Use the Date Picker to enter a date with a consistant format

To add a date there is an active X element within VBA accessible to Excel 2010 called the DatePicker. You can add it directly to a form or a worksheet. Go to the Developer tab in Excel 2010. If the developer tab not visible, go File>Options then add it to the ribbon. On the Developer Tab click the insert menu icon and add it by clicking more controls. Scroll to the Microsoft Date and Time Picker Control, version 6.0, click it and then OK. Your pointer will be a cross hair. Drag the cursor on the sheet to draw the control. Alternately, you can do this in the VB Editor.

33534-Datepicker2

Once the Editor is open, create a userform then add the DatePicker to it using the same technique, an OK button and a Clear button. In this example, to access the date Picker on a userform when someone clicks on a certain cell, you must add some simple code and the DatePicker will open and the user selects a date. It then places the date in the cell or wherever you desire. Here is what it looks like when open:

32282-DatePicker

Place the following code in the userform module sheet

Code:

Private Sub CommandButton1_Click()
‘OKButton gives the active cell the sected date then closes form
ActiveCell.Value= DTPicker1.Value
SetDate.Hide
End Sub

Private Sub CommandButton2_Click()
‘Clearbutton sets the active cell to blank and closes form
ActiveCell.Value= “”
SetDate.Hide
End Sub

Private Sub UserForm_Activate()
‘Setsthe default date to current date for the DatePicker when the form opens
DTPicker1.Value= Date
End Sub

Place the following code in the Worksheet_SelectionChange event subroutine of the worksheet’s module

Code:

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = “$A$1” Then SetDate.Show
End Sub

Reply | Quote

Mary,
This is a response specific to your situation. Here is the code you will need to enter a date into a textbox (Textbox1) on a userform (Userform1) from a datepicker control (DTPicker1) from a second userform (Userform2) and an “OK” button. Below it, I have added one possible explanation to the mal-behavior you are experiencing.

Userform1.Textbox1

Code:

Private Sub TextBox1_Enter()
UserForm2.Show
End Sub

Userform2.CommandButton1

Code:

Private Sub CommandButton1_Click()
UserForm1.TextBox1.Value = DTPicker1.Value
UserForm2.Hide
End Sub

Userform2

Code:

Private Sub UserForm_Activate()
‘Sets the default date to current date for the DatePicker when the form opens
DTPicker1.Value = Date
End Sub

33542-DTPicker

As far as you situation Mary,
Many times the behavior you are experiencing is from an overlooked setting. If you look at the example I post here, Userform1 opens presenting with a label and a Textbox1. There is code written in the Textbox1_Enter event that when clicked, Userform2 will show. IMPORTANT: If the tab setting for the TextBox1 is set to TRUE, as soon as Userform1 is initalized, the focus will shift immediately to TextBox1 and the code will fire. Userform1 will not be presented and Userform2 will be activated. I believe this may be occuring in your situation. An enabled tab stop is giving focus to an object and code for that object unexpectedly runs. Or perhaps, something is making the code to open userform2 close prematurely. You can experiment with this in my example by toggling the Tab stop back and forth for Userform1.TextBox1. Then run the code to initiate Userform1. When Tabstop=True, you will be presented with Userform2. When False, You will be presented with userform1.

33543-Tabs

You may have code in the Enter event handler of the DTPicker and its Tab stop set to true. When the form is initialized, focus is shifted to the DTPicker, its code is run immediately, and if it contains “Userform2.hide”, it will immediately close the form. This is why I prefer an “OK” button. Setting the Tab stop to False will correct this.

HTH,
Maud

Reply | Quote