Forum Discussion
Edg38426
Jun 16, 2022Brass Contributor
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