Moving values from one form to another

Posted on 2006-05-01
Last Modified: 2010-04-07
Hoping somebody can help me out here. I'm working on some forms in Excel. One's a data input form and the other's a calendar.

I've got several controls that take dates as input. In the enter event for each control, I want to be able to open the calendar, pick a date, and return the date to the control that I originally clicked. I can do it manually pretty easy within the calendar:
Form.controlname.value = Calendar1.value
The problem is I have punch in the specific name of the control. Your thoughts?
Question by:jdcollins21
    LVL 19

    Expert Comment


    just do it like this: vbModal '<--halts code on this form and turns over execution to modal form.
    frmThisForm.controlname.value = frmCalendar.value

    Then remove Form.controlname.value = Calendar1.value from the calendar form.

    This way all the calendar does is allow the user to select the date, then when the calling form gets back it just grabs that value.

    Make sense?


    Author Comment

    It's pretty close to what I'm after. Two things.

     Is there a way to get the code to return to the control without specifically naming the control (like active control or storing and referencing the control name)?

    Also, using your method, no matter what date I pick on the calendar form, the control value on the input form always jumps back to the original calendar value.
    LVL 19

    Expert Comment

    I dont think so jdcollins21.
    LVL 4

    Expert Comment

    You would need to move the date using the Calendar.OnClick function.  That way everytime the calendar is clicked, you can pass the new date.  To test this, put this code in, substituting Calendar1 with the name of your calendar control:

    Private Sub Calendar1_Click()
    MsgBox (Calendar1.Value)
    End Sub

    That should pop up a box with the correct date everytime you click on the calendar.
    LVL 6

    Accepted Solution

    Of course you can access a control via its name, just like the following:

        UserForm1.Controls("TextBox1").Text = "aaa"


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now