Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open a form from a form VBA, Excel 2010

    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?

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    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.

    Datepicker2.jpg

    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:

    DatePicker.jpg

    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
    Last edited by Maudibe; 2013-03-30 at 12:20.

  5. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts

    DatePicker

    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
    DTPicker.jpg

    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.

    Tabs.jpg

    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
    Last edited by Maudibe; 2013-03-30 at 13:29.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •