MS Excel - Set UserForm Object Variable from another UserForm

C_Patrick
C_Patrick used Ask the Experts™
on
I'm using MS Excel 2007 UserForms to gather parameter information used with SQL stored procedures.  Each UserForm contains at least one textbox used to capture a date value.  I've been trying to make use of a single userform with a calendar control to set the various date field values found on my collection of userforms, but have not been successful at setting an object variable on the calendar userform to a reference of the calling textbox.

Within the code behind the calendar control's userform, I've created a public variable like so:
     Public dateControl As MSForms.textbox

The procedure used to set the associated textbox value is:
     Private Sub Calendar1_Click()
         dateControl.Text = Calendar1.Value

     End Sub

The code used to set the dateControl to a reference of a textbox on another userform is:
     Private Sub imgBeginDate_Click()
         frmCalendar.Show
         Set frmCalendar.dateControl = frmEarnedHours.Controls(txtBeginDate)
     End Sub

Does anyone know how to perform this reference to a textbox on another userform, or am I going to have to create a specific calendar for each textbox which captures a date?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
You have it backwards. Create a public variable for the text control, not the calendar control. Then set the calendar control's public variable to the desired text box control and then the calendar control will know what to do.

Kevin
Top Expert 2008

Commented:
Wait...that IS what you did. OK, now I'm wondering why it isn't working.

Kevin

Author

Commented:
I should have also mentioned that the error that I receive is: "Object variable or With block not set," when the Calendar1_Click event fires.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2008
Commented:
Yes, that code works. What is the problem? Why do you say it isn't working?

Kevin

Author

Commented:
The code breaks within the Private Sub Calendar1_Click() event, when setting dateControl.text = Calendar1.Value.  dateControl equals nothing.

Author

Commented:
Kevin,

You were correct.  The code works... If the object variable reference is set BEFORE using the form.show() command!  Like this...

     Private Sub imgBeginDate_Click()
          Set frmCalendar.dateControl = Me.txtBeginDate
          frmCalendar.Show
     End Sub

I had this sequence mixed up in the previous attempts.  Thanks for your quick responses!

Author

Commented:
Solution was discovered by person posing the question.
Top Expert 2008

Commented:
I missed that! You are showing the forms modally which means execution stops at .Show until the other (calendar) form is closed.

Kevin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial