Forum Discussion

Edg38426's avatar
Edg38426
Brass Contributor
Jun 16, 2022

Using VBA, How to run a Macro inside of a newly added row from user form

I have a user form that I have created with VBA that inputs data into a table (table name "tblLedger") after the form is submitted.

 

I also have a separate Macro that inserts a value from another sheet into a cell in that same table into column O (the macro is named "New_WO").

 

Is there a way that I can include that macro into the VBA code that is adding the new table row, that way it can all happen together when the form is submitted?

 

Here is the whole thing. I also have it sending an email after the form data is submitted, and recalculating the worksheet to enable my conditional formatting:

 

 

 

Private Sub cbsubmit_Click()
Dim DateRequested As String
DateRequested = txtDateRequested.Text

Dim Priority As String
Priority = cbPriority.Text

Dim Name As String
Name = txtName.Text

Dim JobType As String
JobType = cbJobType.Text

Dim PlanNumber As String
PlanNumber = txtPlanNumber.Text

Dim PlanName As String
PlanName = txtPlanName.Text

Dim Elevations As String
Elevations = txtElevations.Text

Dim FilePath As String
FilePath = txtFilePath.Text

Dim StructuralOptions As String
StructuralOptions = txtStructuralOptions.Text

Dim Community As String
Community = txtCommunity.Text

Dim Address As String
Address = txtAddress.Text

Dim Lot As String
Lot = txtLot.Text

Dim Block As String
Block = txtBlock.Text

Dim Description As String
Description = txtDescription.Text

Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets("Work Orders")

Set tbl = wsh.ListObjects("tblLedger")

Dim lRow As ListRow
Set lRow = tbl.ListRows.Add

With lRow
.Range(1) = DateRequested
.Range(2) = Name
.Range(3) = JobType
.Range(4) = Priority
.Range(5) = PlanNumber
.Range(6) = PlanName
.Range(7) = Elevations
.Range(8) = StructuralOptions
.Range(9) = Community
.Range(10) = Address
.Range(11) = Lot
.Range(12) = Block
.Range(13) = FilePath
.Range(14) = Description
End With

'Updated by Extendoffice 2017/9/14
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "A new Architectural Work Order has been created" & vbNewLine & vbNewLine & _
              "Please see attached and review the Architectural Work Order Workbook to note the new work requested." & vbNewLine & vbNewLine & _
              "Date Reguested: " & Me.txtDateRequested.Value & vbNewLine & "Name: " & Me.txtName.Value & vbNewLine & "Job Type: " & Me.cbJobType.Value & vbNewLine & "Priority: " & Me.cbPriority.Value & vbNewLine & "Plan Number: " & Me.txtPlanNumber.Value & vbNewLine & "Plan Name: " & Me.txtPlanName.Value & vbNewLine & "Elevations: " & Me.txtElevations.Value & vbNewLine & "File Path: " & Me.txtFilePath.Value & vbNewLine & "Strucural Options: " & Me.txtStructuralOptions.Value & vbNewLine & "Community: " & Me.txtCommunity.Value & vbNewLine & "Address: " & Me.txtAddress.Value & vbNewLine & "Lot: " & Me.txtLot.Value & vbNewLine & "Block: " & Me.txtBlock.Value & vbNewLine & "Work Description: " & Me.txtDescription.Value & vbNewLine & vbNewLine
                  On Error Resume Next
    With xOutMail
        .To = "email address removed for privacy reasons"
        .CC = ""
        .BCC = ""
        .Subject = "New Architectural Work Order Submitted"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing

Application.CalculateFull

End Sub

 

 

 

No RepliesBe the first to reply

Resources