Calling a Macro pretty simple yet...

[erutherford]

I have a form that enters data in Col A, B, C.

A macro runs next that groups the data into Col D

Here is the macro ( runs fine manually)

Sub MyCarsCombined()
Dim lngLastRow As Long

Application.ScreenUpdating = False

'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)
Application.ScreenUpdating = True
End Sub

But when I call for the macro in the sub it won't run. I have tried moving it in a number of places, but no luck. Manually fine

here is where I call for it

Private Sub CommandButton3_Click() 'Submit ufCars
Dim answer As Integer
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"
  cbxYear.BackColor = vbYellow
  Exit Sub
End If

'check Make
If Trim(Me.tbxMake.Value) = "" Then
  tbxMake.SetFocus
  MsgBox "Please enter Make of the Car. Required Field"
  tbxMake.BackColor = vbYellow
  Exit Sub
End If

'check License Plate
If Trim(Me.tbxLicense.Value) = "" Then
  tbxLicense.SetFocus
  MsgBox "Please enter License Plate Info. Required Field"
 tbxLicense.BackColor = vbYellow
  Exit Sub
End If
  
 If cbxYear.BackColor = vbYellow Then
 cbxYear.BackColor = vbWhite
 End If
 
 If tbxMake.BackColor = vbYellow Then
 tbxMake.BackColor = vbWhite
 End If
 
 If tbxLicense.BackColor = vbYellow Then
 tbxLicense.BackColor = vbWhite
 End If
  
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
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
      
End With

Call MyCarsCombined ' Macro

'clears 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 = ""
 
answer = MsgBox("Want to add another car?", vbQuestion + vbYesNo + vbDefaultButton2, "Add another Car")
If answer = vbYes Then

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 = ""

Else
    
  Unload ufAddCar
  Call Dashboard_Open

End If

Call MyCarsCombined ' Macro

End Sub

ideas????

 

[CSmith]

Do you get error? or?

 

[erutherford]

No error. Just doesn't run when called. I also have it in the Form_Initialize (its the only line). If I step through each line, it will perform correctly.
I just ran the form and now it works, which it has done before. Intermittent. I am trying to duplicate the problem again

 

[erutherford]

The form writes to a worksheet called "MyCars". If the form is opened from the dashboard then the marco will NOT run. If I open the form with MyCars worksheet showing behind the form then the macro runs fine.

 

[CSmith]

good form is also call Set ws = nothing before exiting/unloading.

Set ws = Worksheets("MyCars")

Try this:

Set ws = Sheets("MyCars")

 

[erutherford]

Made the change, but results - The form writes to a worksheet called "MyCars". If the form is opened from the dashboard then the marco will NOT run. If I open the form with MyCars worksheet showing behind the form then the macro runs fine.

 

[erutherford]

good form is also call Set ws = nothing before exiting/unloading.

Not sure was you mean?

 

[CSmith]

At the end of a macro you should always clear variables that were SET.

 

[CSmith]

Made the change, but results - The form writes to a worksheet called "MyCars". If the form is opened from the dashboard then the marco will NOT run. If I open the form with MyCars worksheet showing behind the form then the macro runs fine.

Try this:

Sub MyCarsCombined()
  Dim lngLastRow As Long, ws As Worksheet
  Set ws = ActiveWorkbook.Sheets("MyCars")
  Application.ScreenUpdating = False
    With ws
      '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 With
  Application.ScreenUpdating = True
  Set ws = Nothing
End Sub

 

[erutherford]

That answered my next question, how to set variables to nothing. I'll insert this and test. Standby....