Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Annie Russell   on Dec 29 In MS Office Category.

  
Question Answered By: Frances Parker   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
Else
Calendar.Value = Date
End If
End With
Show
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()
Hide
mField.Value = Format(Date, gDF)
End Sub

and the calendar control click is

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


Notes:

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
.SetFocus
End With
Show
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)
Hide
End Sub

Private Sub CancelButton_Click()
Hide
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.

Share: 

 

This Question has 4 more answer(s). View Complete Question Thread

 
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.


Tagged: