Pass multiple entries between Userforms on VBA

Question

So I am trying to a create userform. I currently have two forms, the first is general data for a truck, once the user has entered all the appropriate text box's the second userform will display and transfer the data onto the Truck Info label for reference. (I got this transfer figured out)

1st User Form

I want the user to have the ability to add multiple part numbers & qty for each specific/Unique trailer (usually have more than 1 part). So when the user clicks the "Add Part Number" Button it will add a new row into the details and return focus to Part number Combo Box like I did with the first user form so they can start on the next part

2nd Form

Here is my code for the 1st form

Private Sub UserForm1_Initialize()
txtTrailerNum.SetFocus
txtTrailerNum.Value = ""
txtScacCode.Value = ""
txtTruckNum.Value = ""
txtSupNum.Value = ""
txtInvoiceNum.Value = ""
txtInvoiceType.Value = ""
txtOrgRef.Value = ""
 End Sub

Private Sub Clear_btn_Click()
txtTrailerNum.Value = ""
txtScacCode.Value = ""
txtTruckNum.Value = ""
txtSupNum.Value = ""
txtInvoiceNum.Value = ""
txtInvoiceType.Value = ""
txtOrgRef.Value = ""
 End Sub

Private Sub Enter_btn_Click()

If txtTrailerNum.Text = "" Then
    MsgBox "Please Enter a valid Trailer Number."
    txtTrailerNum.SetFocus
    Exit Sub
End If

If txtScacCode.Text = "" Then
    MsgBox "Please Enter a valid SCAC Code."
    txtScacCode.SetFocus
    Exit Sub
End If

If txtTruckNum.Text = "" Then
    MsgBox "Please Enter a valid Truck Number."
    txtTruckNum.SetFocus
    Exit Sub
End If

If txtSupNum.Text = "" Then
    MsgBox "Please Enter a Supplier Number."
    txtSupNum.SetFocus
    Exit Sub
End If

If txtInvoiceNum.Text = "" Then
    MsgBox "Please Enter a valid Invoice Number."
    txtInvoiceNum.SetFocus
    Exit Sub
End If

If txtInvoiceType.Text = "" Then
    MsgBox "Please Enter a valid Invoice Number."
    txtInvoiceType.SetFocus
    Exit Sub
End If

If txtOrgRef.Text = "" Then
    MsgBox "Please Enter a valid Oiginator Reference."
    txtOrgRef.SetFocus
    Exit Sub
End If

UserForm2.Show

End Sub

Here is the code for my 2nd Form

Private Sub AddPrtNumbtn_Click()

End Sub

Private Sub PartNumcbo_AfterUpdate()
On Error Resume Next         
txtPartDesc=Application.WorksheetFunction.VLookup(PartNumcbo.Value,
                                     Range("PartDesc"), 2, False)
If Err.Number <> 0 Then
MsgBox "Invalid Part Number"
txtPartDesc.Value = ""
 End If
 On Error GoTo 0
 End Sub


 Private Sub UserForm_Initialize()
 PartNumcbo.RowSource = "PartDescData!A:A"
 TruckInfolbl.Caption = "Trailer Num: " & UserForm1.txtTrailerNum.Value & "    
 " & "SCAC Code: " & UserForm1.txtScacCode.Value & " " & "Truck Number: " &   
 UserForm1.txtTruckNum.Value & "  " & "Supplier Number: " & 
 UserForm1.txtSupNum.Value & " " & "Invoice Number: " & 
 UserForm1.txtInvoiceNum & " " & "Invoice Type: " & 
 UserForm1.txtInvoiceType.Value & " " & "Originator Reference: " & 
 UserForm1.txtOrgRef.Value

 End Sub

Ideally when the form is completed the part numbers would be related to the truck from the first form and then input onto a worksheet range. So if the user clicked the create trailer button on the bottom of the 2nd form this would be input into a worksheet.

How would I go about passing data from the combo box and two txtboxes on the 2nd Userform to the Inventory Details Label, and then input that into a range???

This is begging for an OOP solution. Just knock together a "Truck" class that you can pass around. — Comintern
– Comintern, Commented Feb 17, 2017 at 16:57
I miss at least one question mark in your question. What exactly are you asking? — gembird
– gembird, Commented Feb 17, 2017 at 16:58
Your code will break if you or a future maintainer starts treating the forms as the objects they are, and work off a new instance every time - because UserForm1.Whatever is changing state in the default instance - if you need two forms to talk to each other, have one receive a reference to the other, don't just grab the default instance. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 17, 2017 at 17:02

gembird · Accepted Answer · 2017-02-17 17:40:52Z

One form will be parent and one child. Parent will have reference to child and will create it. When child is finished with its work it will raise an event with all the data it has collected etc. Parent will subscribe to this event and so it will receive the data.

Note: As @Comintern suggest it might be appropriate to wrap all the data in a class and pass instance of this class.

Here an example, HTH.

Parent Form

Option Explicit

Private WithEvents m_childForm As UserFormChild

Private Sub CommandButton1_Click()
    Set m_childForm = New UserFormChild
    m_childForm.Show
End Sub

Private Sub m_childForm_ProcessData(ByVal comboBoxData As String, ByVal textBoxData1 As String, ByVal textBoxData2 As String)
   Me.TextBox1.Text = "Data: " & comboBoxData & ", " & textBoxData1 & ", " & textBoxData2
End Sub

Child Form named UserFormChild

Option Explicit

Public Event ProcessData(ByVal comboBoxData As String, ByVal textBoxData1 As String, ByVal textBoxData2 As String)

Private Sub CommandButton1_Click()
    RaiseEvent ProcessData(Me.ComboBox1.Text, Me.TextBox1.Text, Me.TextBox2.Text)
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Me.ComboBox1.AddItem "A"
    Me.ComboBox1.AddItem "B"
    Me.ComboBox1.AddItem "C"
    Me.ComboBox1.ListIndex = 1
End Sub

Collectives™ on Stack Overflow

Pass multiple entries between Userforms on VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related