Passing Userform Vairiable to Main Module

[MAM8433]

Right now, my macro starts Module1 [Sub simpleXlsMerger()], which opens a userform that collects start and end dates using 2 datepicker controls. The dates are collected in another sub procedure [Private Sub CmdOK_Click()]. How do I pass these dates back to Module1? I’ve read that I need to use “public” instead of Dim, but it doesn’t work with Subs. Instead, I get the message, “Invalid attribute in Sub or Function.”

What changes can I make to pass these dates back to Module1? And where do the Public variable declarations go? In Module1 or CmOK_Click()? Thank you. I hope you can help.

 

[VBA Geek]

your module1:

Sub simpleXlsMerger()
Dim UForm As UserForm1


Dim StartDate As Date
Dim EndDate As Date


Set UForm = New UserForm1
UForm.Show vbModal


StartDate = UForm.StartDate
EndDate = UForm.EndDate


Set UForm = Nothing


End Sub

and your useform module:

Private dtStartDate As Date
Private dtEndDate   As Date


Property Get StartDate() As Date
    StartDate = dtStartDate
End Property


Property Get EndDate() As Date
    EndDate = dtEndDate
End Property


Private Sub cmdOK_Click()
    dtStartDate = #1/1/2000# '  change to dt picker value
    dtEndDate = #5/9/2004#   '  change to dt picker value
    Me.Hide
End Sub

 

[MAM8433]

Thank you for the code. This code is new to me; please bear with me.

Because my useform is called frmCalendar, I replaced "Uform" with "frmCalendar."

I do have compilation errors, starting with the statement Dim frmCalendar as UserForm1. I get this error message: User-defined type not defined. I use Excel 2013; when I code Dim frmCalendar as UserForm and not UserForm1, the coded is acceptable to the compiler.

The next issue occurs with the code: set frmCalendar = New Userform. It generates the error message: Invalid Use of New keyword. Do I need this code? If I turn it into a comment, the frmCalendar.show vbModal shows the error: Object variable or With block variable not set. I hope you can help me straighten this out.

 

[MAM8433]

Hello! I should have included my code. I need to pass start and end dates from date picker controls in a userform to Module1. Right now, my macro opens Module 1, which in turn opens the userform. Please let me know if I have to change that and how to successfully pass the dates. Thank you.

Start of Module1:

Sub simpleXlsMerger()

'This code opens csv files in a specific location and copies data to one destination xlsx workbook.
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Dim folderName As String
Dim UplRow As Long, wkshtNum As Integer, DesRow As Long
Dim StartDate As Date, EndDate As Date

frmCalendar.Show
Debug.Print StartDate, EndDate

Unfortunately, the dates aren't in Module1.startdate and enddate.

Userform code:

Private Sub CmdOK_Click()

Dim StartDate As Date, EndDate As Date

StartDate = DTPicker1
Debug.Print StartDate

EndDate = DTPicker2
Debug.Print EndDate

If EndDate < StartDate Then
MsgBox "End date is before start date. Start again"
End If

Unload frmCalendar
End Sub

Private Sub CommandButton2_Click()
Cancel frmCalendar
End Sub

 

[VBA Geek]

You need to have a userform named frmCalendar:

Inside this userform you have two datepickers named DTPicker1 and DTPicker 2 (I used textboxes in my example because I dont have the dtpicker installed as I have developed my own version of a calendar) and a command button called Ok

so the code inside this userform is:

Option Explicit


Private dtStart As Date
Private dtEnd   As Date


Property Get StartDate() As Date
    StartDate = dtStart
End Property


Property Get EndDate() As Date
    EndDate = dtEnd
End Property




Private Sub cmdOK_Click()
    ' add your dates check
    ' if dtEnd < dtStart etc etc...
    dtStart = Me.DTPicker1.Value
    dtEnd = Me.DTPicker2.Value
    
    Me.Hide
End Sub

and the code in module 1 is:

Sub simpleXlsMerger()


Dim U As frmCalendar


Set U = New frmCalendar


Call U.Show(vbModal)


MsgBox U.StartDate ' or store in a variable for later use
MsgBox U.EndDate


Set U = Nothing




End Sub

 

[MAM8433]

Hello. First, thank you for your patience, while I tended to the decline and death of a parent. My code currently looks like this:

Module1 begins with:

Sub simpleXlsMerger()

'This code opens csv files in a specific location and copies data to a destination xlsx workbook.

Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Dim folderName As String
Dim UplRow As Long, wkshtNum As Integer, DesRow As Long
Dim StartDate As Date, EndDate As Date
Dim U As frmCalendar

Set U = New frmCalendar

Call U.Show(vbModal)

StartDate = U.StartDate
EndDate = U.EndDate

Set U = Nothing

'open folder picker message box….

-----
My userform (frmCalendar) code now looks like this:

Option Explicit

Private DtStart As Date
Private DtEnd As Date

Property Get StartDate() As Date
StartDate = DtStart
End Property

Property Get EndDate() As Date
EndDate = DtEnd
End Property

Private Sub cmdOK_Click()
If DTPicker2 < DTPicker1 Then
MsgBox "End date is before start date. Start again"
Unload frmCalendar
End If

If DTPicker1 <= DTPicker2 Then
DtStart = Me.DTPicker1.Value
DtEnd = Me.DTPicker2.Value
Me.Hide
End If
End Sub

Private Sub CommandButton2_Click()
‘cancel button has been clicked
Unload frmCalendar
End Sub
-------
Unfortunately, I’m in over my head. I have some very fundamental questions:
1. Would you please explain what dtStart and dtEnd do? I don’t understand why I need them when I have DTPickers and StartDate/Enddate.
2. Please explain what “property get” does.
3. Why assign dtStart to StartDate (or dtEnd to EndDate) when I haven’t given dtStart or dtEnd a value?
4. When does this code run? When I use F8, this code is skipped.

Again, thank you for your assistance. I appreciate your help.

 

[VBA Geek]

sorry to hear about your loss..

Would you please explain what dtStart and dtEnd do? I don’t understand why I need them when I have DTPickers and StartDate/Enddate

They hold the values of the DTpicker(s) which then are passed to the main routine "simpleXlsMerger" via the GET properties

you can read more about classes HERE

Once you have loaded the userform, the userform code will run in response of the actions you take (in your case the click of a command button) and when you unload or hide the userform, the code within "simpleXlsMerger" will resume at the line just after where you loaded the userform

 

[MAM8433]

VBA Geek: Thank you so much! The code works. I read the article and a second one on class modules. It’s still difficult for me to grasp the concepts. I was able to step through the code to see the get property processing, so that was helpful.

I do have a related question to how the code is set up. I want today’s date to appear as the default in the datepicker windows. I can’t figure out where to place the code: DTPicker1. Value= date. It doesn’t belong in the sub CMDOK_Click, as that’s too late in the process. I get compile errors if I place it under the Option Explicit code. Instead of calling the userform in Module 1, should I call a new module that initializes the userform? Or does the class code need to change?

 

[hotabae]

It doesn’t belong in the sub CMDOK_Click, as that’s too late in the process. ... Instead of calling the userform in Module 1, should I call a new module that initializes the userform?

You should include any "initialization" code inside the Userform code as a sub with this format:

Private Sub UserForm_Initialize()

     'Initialization Code

End Sub

No matter what your userform is named, the sub should always read "UserForm_Initialize()"

 

[MAM8433]

I keep getting closer. When I open my file for the first time, the Compile VBAProject option in the Debug tab is not lit. I added this code to the userform:
Private Sub UserForm_Initialize()
DTPicker1.Value = Date
DTPicker2.Value = Date
End Sub

Then when I recompile, Module 1 code StartDate.Value = U.StartDate throws the Invalid qualifier error message and StartDate is highlighted. I exited without saving and reopened. It seems that any change I now make (e.g., I retyped End Property), the compiler throws the same error for the same line of code. Why? Me.startdate.value doesn't cure this.