MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Reference form values dynamically from another form?

  Asked By: Annie    Date: Dec 29    Category: MS Office    Views: 1177

Here is my problem
I have a userform for a task list which has 20 breakdowns by date ie
Enter [Date1] | enter [xx] hours of work
Enter [Date2] | enter [xx] hours of work
Enter [Date20] - enter [xx] hours of work

I created a frmCalendar userform so that users click on a calendar
instead of entering Date1, etc.
How do I make it so that for each date, I call the same form
(frmCalendar), and then the value from the calendar is transferred
to the appropriate place (labelDatexx on frmBrkDown)?
It works fine for one Date, ie:
'this is on the fmrBrkDown code
Sub Date1_Click()
End Sub

'This is on frmCalendar code, cal is the calendar
Sub Cal_click
frmBrkDown.LabelDate1.Caption = cal.value
Unload Me
End Sub
but what about 20 dates?
Obviously I can have 20 calendar userforms, one for each date but
there's got to be a way to reference the Calendar Value from the
same form.

I tried
Sub Date1_Click()
LabelDate1.Caption = frmCalendar.Cal.Value
End Sub

Sub frmCal_click
Unload Me
End Sub
but that did not work, it gave always the same date.



5 Answers Found

Answer #1    Answered By: Khadeeja Malik     Answered On: Dec 29

Try declaring a global object variable in a module, to represent any of your 20
date labels as needed:
Global WhichOne as Object

Then, in Date1_Click(), add
Set WhichOne = frmBrkDown.LabelDate1
before loading your calendar  form. You must
make a similar change for each of your other Date_Click subs.

Change your calendar form  code to
WhichOne.Caption = cal.value
Unload Me

Answer #2    Answered By: Shayan Anderson     Answered On: Dec 29

Thank you so much, that worked perfectly!

Answer #3    Answered By: Frances Parker     Answered On: Dec 29

One way to do it is to pass a reference  to the field to an Initialise subroutine
in the form  that holds the calendar. So, the click  events in your other form
would be something like:

Private Sub FromDateCalendar_Click()
Call CalendarPopUp.Initialise(FromDate)
End Sub

Private Sub ToDateCalendar_Click()
Call CalendarPopUp.Initialise(ToDate)
End Sub

Then the CalendarPopUp form would have an Initialise subroutine like:

Public Sub Initialise(ByRef Field As Control)
Set mField = Field
With Field
If Is(.Value) Then
Calendar.Value = .Value
Calendar.Value = Date
End If
End With
End Sub

Where it first plugs the date  (if any) from the field into the calendar  control
- or presets the calendar control to "today". Then it shows the form.

I always put a "today" button on my pop-up calendar form. Its event is

Private Sub TodayButton_Click()
mField.Value = Format(Date, gDF)
End Sub

and the calendar control click is

Private Sub Calendar_Click()
mField.Value = Format(Calendar.Value, gDF)
End Sub


FromDate and ToDate are the text fields for the dates. FromDateCalendar and
ToDateCalendar are buttons associated with them, and it's these buttons that I
click to bring up the calendar popup.

mField is a global private Control variable in the calendar popup form.

gDF is a global date format string - in my case "dd-mmm-yyyy".

There are other ways to get the information back. For instance, in a form that
contains a comment entry text box and returns a string that is not necessarily
going to be put into a form field, I return the information via a ByRef
parameter in the Initialise call:

Public Sub Initialise(ByRef Comment As String)
mComment = Comment
With CommentBox
.Value = mComment
End With
Comment = mComment
End Sub

In this case, the initialise subroutine holds on the Show call. The OK button
sets the global variable and hides the form, while cancel just hides the for.:

Private Sub OKButton_Click()
mComment = Trim(CommentBox.Value)
End Sub

Private Sub CancelButton_Click()
End Sub

If you want to be really flexible, you could make the parameter in the
Initialise subroutine into a ByRef Variant, ask it whether it is pointing at a
field or a cell or a VBA variable and act accordingly.

Answer #4    Answered By: Adalgisa Fischer     Answered On: Dec 29

I tried this solution as it quicker to implement and it worked.
I am a little confused by these references (ByRef), and the mention
of "global private control" variables as I am fairly new to VBA.
I will play with them on the weekend as it will hopefully clarify my
understanding of VBA variables. I always default my calendars (on
initialize) to today or the value (if prev. entered), however I
never thought to include a "today" button on the form. Thanks, that
makes perfect sense. I mentioned it to my test users  and they love
it, and they also want a Yesterday button :)

Answer #5    Answered By: Meenakshi Khochar     Answered On: Dec 29

ByRef is the default anyway. The opposite is ByVal. If you want the subroutine
to change things back in the caller's area, you need ByRef because ByVal takes a
private copy for the subroutine.

Showing ByRef explicitly - even though it is the default - is good programming

A global private control (yes I agree - very clumsy wording, sorry) was meant to
say, put a

Private mField As Control

at the top of your module, before any subroutines or functions, but after your

Option Explicit

It declares a Control variable of this name that is private to the module, but
available (global) to all the code  inside the module. The lower-case "m" at the
start of the name is a historical way of prefixing such module-wide variables
(it started with object-oriented programming, but is a useful convention).

Hutch's solution is along the same lines as mine - having the Object or Control
variable and getting the OK click  to put the value back there. What I've done
is to encapsulate all of this inside the module, so that you only need a single
line to call  it from wherever you need it.

I discourage the use of global variables, and particularly of having the code
"force feed" information into service modules. I much prefer to supply the
information to the service module inside its own calling sequence, and then let
it do what it needs to do, privately.

Didn't find what you were looking for? Find more on Reference form values dynamically from another form? Or get search suggestion and latest updates.