Avatar of C_Patrick
C_PatrickFlag for United States of America

asked on 

MS Excel - Set UserForm Object Variable from another UserForm

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!
Microsoft ApplicationsMicrosoft Excel

Avatar of undefined
Last Comment
zorvek (Kevin Jones)
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

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
Wait...that IS what you did. OK, now I'm wondering why it isn't working.

Kevin
Avatar of C_Patrick
C_Patrick
Flag of United States of America image

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of C_Patrick
C_Patrick
Flag of United States of America image

ASKER

The code breaks within the Private Sub Calendar1_Click() event, when setting dateControl.text = Calendar1.Value.  dateControl equals nothing.
Avatar of C_Patrick
C_Patrick
Flag of United States of America image

ASKER

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!
Avatar of C_Patrick
C_Patrick
Flag of United States of America image

ASKER

Solution was discovered by person posing the question.
I missed that! You are showing the forms modally which means execution stops at .Show until the other (calendar) form is closed.

Kevin
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo