VBA: Passing data from a form to a sub without creating / calling the form

35 Views Asked by At

The aim of my code is to read data from an excel sheet as well as a UserForm to create emails to be sent out with details. It is partly based on this tutorial: https://excelmacromastery.com/vba-userform/

I currently have three parts to my code:

  • Userform code In my Module:
  • SendEmails
  • DisplayForm

DisplayForm creates the userform and initialises it/shows it. DisplayForm is called from the main sub SendEmails. I cannot find a way to use the form variables in SendEmails.

Sub SendEmails()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim ws As Worksheet
    Dim iRow As Integer
    Dim FilterItem As String
    Dim FilterColumn As Range
    Dim FilterRange As Range

    Call DisplayForm
      
    Set OutApp = CreateObject("Outlook.Application")
    Set ws = ThisWorkbook.Sheets("Test") 'Change to the name of your sheet
    iRow = 2 'Start from the second row (assuming headers in row 1)
    
    FilterItem = Trade
    Set FilterColumn = ws.Columns("C") ' Change "C" to the column letter that contains the trade info
    Set FilterRange = FilterColumn.Find(What:=FilterItem, LookIn:=xlValues)
    
    MsgBox "Filter Item: " & FilterItem
    MsgBox "Trade: " & Trade
    
    If Not FilterRange Is Nothing Then
        Do Until IsEmpty(ws.Cells(iRow, 1))
           If ws.Cells(iRow, FilterColumn.Column).Value = FilterItem Then

''Rest of code to create email below
Sub DisplayForm()

    ' Create form
    Dim frm As New UserForm1
   '' Set frm = New UserForm1

    ' Display the UserForm
    frm.Show
    
    ' Check if the user cancelled the UserForm
    If frm.Cancelled = True Then
        MsgBox "The UserForm was cancelled."
    Else
        MsgBox "You entered:" & vbCrLf & _
                "Project name: " & frm.ProjectName & vbCrLf & _
                "Doc transfer method: " & frm.DocTransfer & vbCrLf & _
                "Start and finish date: " & frm.Start & " and " & frm.Finish & vbCrLf & _
                "Price Type: " & frm.Prices & vbCrLf & _
                "Trade: " & frm.Trade & vbCrLf & _
                "Quote due date: " & frm.DueDate & vbCrLf & _
                " "
    End If
       
     ' Clean up
    Unload frm
    Set frm = Nothing

End Sub

I have tried:

  • assigning values to variables in DisplayForm and then passing them to SendEmails.
  • using values directly (as I have defined them in the public property within the form code)
Public Property Get Info() As String

    PName = ProjectName.Value
    Start = Start.Value
    Finish = Finish.Value
    Due = DueDate.Value

    RFQtype = TypeRFQ.Value
    DocTransf = DocTransfer.Value
    Prices = Prices.Value
    Trade = Trade.Value

End Property
  • Something I was trying to look up but could not find an answer to is: if I cannot use the variables directly then do I need to call the Info function and in that case how would I go about that?

Thank you!

EDIT: I have set the "Dim frm As Userform1" line at the module level instead of in the DisplayForm sub. This seems to solve my issue but I was wondering was there any issue with doing that? does that then mean my "Public Property get Info" is obsolete? (I guess I am not fully sure what that function does here even after having googled extensively)

0

There are 0 best solutions below