Call Macro inside a form?

[erutherford]

Spent a lot of time and haven't found the solution. I have a good working form and a good working macro. I need the macro to run when the "submit" button is selected in the form. Answer is probably looking right at me, but I am not seeing it. I tried "Call" function etc. Has to be a simple, but I could use the help.

Thanks


Form code

Private Sub CommandButton1_Click() 'Submit ufCars
Dim lRow As Long
Dim lActivity As Long
Dim ws As Worksheet
Set ws = Worksheets("MyCars")

'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
'check Years
If Trim(Me.cbxYear.Value) = "" Then
  cbxYear.SetFocus
  MsgBox "Please select a Year. Required Field"
  Exit Sub
End If

'check License Plate
If Trim(Me.tbxLicense.Value) = "" Then
  tbxLicense.SetFocus
  MsgBox "Please enter License Plate Info. Required Field"
  Exit Sub
End If

'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"
   .Cells(lRow, 1).Value = Me.cbxYear.Value
   .Cells(lRow, 2).Value = Me.tbxMake.Value
   .Cells(lRow, 3).Value = Me.tbxModel.Value
   .Cells(lRow, 5).Value = Me.tbxLicense.Value
   .Cells(lRow, 6).Value = Me.tbxColor.Value
   .Cells(lRow, 7).Value = Me.tbxVIN.Value
   .Cells(lRow, 8).Value = Me.tbxPDate.Value
   .Cells(lRow, 9).Value = Me.tbxPAmt.Value
   .Cells(lRow, 10).Value = Me.tbxPMiles.Value
   .Cells(lRow, 11).Value = Me.tbxInsurDate.Value
   .Cells(lRow, 12).Value = Me.tbxRegDate.Value
      
'  .Protect Password:="password"
End With

'clear the data after entry

Me.cbxYear.Value = ""
Me.tbxMake.Value = ""
Me.tbxModel.Value = ""
Me.tbxLicense.Value = ""
Me.tbxColor.Value = ""
Me.tbxVIN.Value = ""
Me.tbxPDate.Value = ""
Me.tbxPAmt.Value = ""
Me.tbxPMiles.Value = ""
Me.tbxInsurDate.Value = ""
Me.tbxRegDate.Value = ""

End Sub

Macro code

Sub MyCarsCombined()
Dim lngLastRow As Long
'Uses Column A to set the 'lngLastRow' variable _
(find the last row) - change if required.
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    Range("D2:D" & lngLastRow).Value = Evaluate("=A2:A" & lngLastRow & "&"" ""&" & "B2:B" & lngLastRow & "&"" ""&" & "E2:E" & lngLastRow)

End Sub

 

[DanteAmor]

Is the macro "MyCarsCombined" inside the userform code?

You already tried

Private Sub CommandButton1_Click()
  Call MyCarsCombined
End Sub

 

[erutherford]

No its not. I think I have tried your code without success. I think its a placement issue. I need the macro to run within the ufCar's code using CmdButton1. FYI the button is an activeX.

 

[erutherford]

Here is where I put the macro at first. (I did add a little more code, but it doesn't change the problem)

Me.cbxYear.Value = ""
Me.tbxMake.Value = ""
Me.tbxModel.Value = ""
Me.tbxLicense.Value = ""
Me.tbxColor.Value = ""
Me.tbxVIN.Value = ""
Me.tbxPDate.Value = ""
Me.tbxPAmt.Value = ""
Me.tbxPMiles.Value = ""
Me.tbxInsurDate.Value = ""
Me.tbxRegDate.Value = ""

Call MyCarsCombined

 
answer = MsgBox("Want to add another car?", vbQuestion + vbYesNo + vbDefaultButton2, "Add another Car")
If answer = vbYes Then................

If I step through it, the code functions just fine. If I use the command button, then the "MyCarsCombined" macro will not run.

 

[erutherford]

Solved!! But I would like to know why! It was a placement issue. Here is where I placed the macro and it now works as it should!

With ws

   .Cells(lRow, 1).Value = Me.cbxYear.Value
   .Cells(lRow, 2).Value = Me.tbxMake.Value
   .Cells(lRow, 3).Value = Me.tbxModel.Value
   .Cells(lRow, 5).Value = Me.tbxLicense.Value
   .Cells(lRow, 6).Value = Me.tbxColor.Value
   .Cells(lRow, 7).Value = Me.tbxVIN.Value
   .Cells(lRow, 8).Value = Me.tbxPDate.Value
   .Cells(lRow, 9).Value = Me.tbxPAmt.Value
   .Cells(lRow, 10).Value = Me.tbxPMiles.Value
   .Cells(lRow, 11).Value = Me.tbxInsurDate.Value
   .Cells(lRow, 12).Value = Me.tbxRegDate.Value
   

End With

Call MyCarsCombined

'clear the data after entry

Me.cbxYear.Value = ""
Me.tbxMake.Value = ""
Me.tbxModel.Value = ""
Me.tbxLicense.Value = ""
Me.tbxColor.Value = ""
Me.tbxVIN.Value = ""
Me.tbxPDate.Value = ""
Me.tbxPAmt.Value = ""
Me.tbxPMiles.Value = ""
Me.tbxInsurDate.Value = ""
Me.tbxRegDate.Value = ""

Logically the macro has to handle the data before its cleared from the form. The just a rookie guess. If someone has the correct explanation, I would love to hear it.

Happy New Year!!!

 

[DanteAmor]

If the call to the macro is inside the macro and there is previously no exit from the sub, then there is no issue.
To know if the macro is running, you can perform the step by step execution by pressing the F8 key.
Or by placing a msgbox inside your macro so that the execution pauses at a certain point.

Glad to know that it already works for you.

Happy New Year for you too!!!