Variable accross form to module

**TonyforVBA** · 09-13-2010, 10:28 AM

I have the below code, which is working perfactly for me.
However, I want to move the 'Sub Event_Date' from the userform to the actual module.
what ammendments do I need to consider rather than just doing a cut and paste on the code?
Cheers guys


'''''''''''''''''''''''''
'''Send Event Report'''
'''''''''''''''''''''''''
Private Sub CommandButton9_Click()

SendDirect = CheckBox3.Value

Call Event_Date
Call Assign_Event_Paths(EventMonth, EventYear)
Call CheckFilesExistLending

End Sub

''''''''''''''''''''''''
'''Dates for Event''''
''''''''''''''''''''''''
Sub Event_Date()

If UserForm1.TextBox5.Value = "" Then
MsgBox ("Enter Month For Report")
NoRangeDate = True
Exit Sub
Else
NoDate = False
EventMonth = UserForm1.TextBox5.Value
EventYear = UserForm1.TextBox6.Value
End If

If UserForm1.TextBox6.Value = "" Then
MsgBox ("Enter Year For Report")
NoRangeDate = True
Exit Sub
Else
NoDate = False
EventMonth = UserForm1.TextBox5.Value
EventYear = UserForm1.TextBox6.Value
End If

End Sub

**romperstomper** · 09-13-2010, 10:36 AM

That doesn't appear to make any sense since the sub just checks if two textboxes are filled in. Why do you want to move it out of the form's code module?

**TonyforVBA** · 09-13-2010, 11:00 AM

I have the Assign Paths to Variable code with in the module, Ive included that below.
I want to move all subs from the UserForm and leave it as a script of Call commands if at all possible.


Sub Assign_Event_Paths(EventMonth As String, EventYear As String)

Dim filepaths(100) As Variant
EventPath = "\\vs1alpfc1\bkcPMU\MIS Reports\Event Report\Results\" & EventYear & "\" & EventMonth & ""

EventBusiness = EventPath & "Bus Event Report " & Format(LendMonth, "mmm") & " " & EventYear & ".xls"
EventPersonal = EventPath & "Personal Event Report " & Format(LendMonth, "mmm") & " " & EventYear & ".xls"

End Sub

**romperstomper** · 09-13-2010, 11:04 AM

All I can see that achieving is creating a need for public variables (which is bad), unless you rewrite all your code. If you want to separate logic from interface (which is a good aim) your button click should call one routine and pass it all the variables required from the form. The called routine should then call the other routines passing local variables as required.

**TonyforVBA** · 09-13-2010, 11:13 AM

Cheers for the advice, I think I'll give it a shot!

**romperstomper** · 09-13-2010, 11:16 AM

For the record, I would keep the validation of the textboxes in the form's code:

Private Sub CommandButton9_Click()
  If Me.TextBox5.Value = "" Then
    MsgBox "Enter Month For Report"
    exit sub
  End If
  If Me.TextBox6.Value = "" Then
    MsgBox "Enter Year For Report"
    Exit Sub
  End If
  RunCodeToDoLotsOfStuffHere
End Sub