Calling a Macro pretty simple yet...

E

erutherford

Active Member
Joined
Dec 19, 2016
Messages
449
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)

Code: 
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

Code: 
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????
 
Sort by date Sort by votes
Update. After some questions and reading, where my code resides is fine. When the workbook first open this code runs perfect.
Code: 
Private Sub workbook_open()
Dim cCar As Range, sh As Worksheet, strAlert As String, dtTest As Date, wkb As Workbook

'This sets the program & insures Col.D is formated correctly

Set sh = ThisWorkbook.Worksheets("Reminders")
Set wkb = ThisWorkbook

    Call MCarData.MyCarsCombined ' Insures cYear, cBrand & cLicense are formated in Col.D ("MyCars)
    wkb.Sheets("Dashboard").Activate
    wkb.Sheets("Dashboard").Range("E2").Select

The important element is the calling of the macro "MCarData.MyCarsCombined" it runs as it should.

But in the same calling of the same macro it will not run
Code: 
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
  
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
 
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 MCarData.MyCarsCombined ' Macro

Set ws = Nothing

End Sub
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Do you have the WORD 'Public' before Sub MyCarsCombined? Also, where is this code placed? module, workbook, userform?
 
Upvote 0
The last code in your MyCarsCombined won't run properly if a different sheet is active. This line:

Code: 
      .Range("D2:D" & lngLastRow).Value = Evaluate("=A2:A" & lngLastRow & "&"" ""&" & "B2:B" & lngLastRow & "&"" ""&" & "E2:E" & lngLastRow)

is missing a dot before the Evaluate call:

Rich (BB code): 
      .Range("D2:D" & lngLastRow).Value = .Evaluate("=A2:A" & lngLastRow & "&"" ""&" & "B2:B" & lngLastRow & "&"" ""&" & "E2:E" & lngLastRow)

to ensure the formula is evaluated in the context of the right sheet.
 
Upvote 0
RoryA said:
The last code in your MyCarsCombined won't run properly if a different sheet is active. This line:

Code: 
      .Range("D2:D" & lngLastRow).Value = Evaluate("=A2:A" & lngLastRow & "&"" ""&" & "B2:B" & lngLastRow & "&"" ""&" & "E2:E" & lngLastRow)

is missing a dot before the Evaluate call:

Rich (BB code): 
      .Range("D2:D" & lngLastRow).Value = .Evaluate("=A2:A" & lngLastRow & "&"" ""&" & "B2:B" & lngLastRow & "&"" ""&" & "E2:E" & lngLastRow)

to ensure the formula is evaluated in the context of the right sheet.
Click to expand...
That was one of the symptoms I described earlier that the code would run only when the effected sheet was active. So is the formula I am using requiring that or is it more a general rule in running macros. I suspect its more the "evaluate" code.
 
Upvote 0
It's the Evaluate code. There are two Evaluate methods - Worksheet.Evaluate which will evaluate a formula in the context of the specified worksheet, and Application.Evaluate which assumes the active worksheet.
 
Upvote 0
RoryA said:
It's the Evaluate code. There are two Evaluate methods - Worksheet.Evaluate which will evaluate a formula in the context of the specified worksheet, and Application.Evaluate which assumes the active worksheet.
Click to expand...
I'll read up on this a bit since this is my first exposure to it.
I added the dot before "Evaluate" and it gives me an error "unqualified ref..", so I am assuming I needed to tell it what sheet I am working with. Since I set the correct worksheet already
Code: 
Set ws = ActiveWorkbook.Sheets("MyCars")
I added ws
Code: 
ws.Evaluate(...
That obviously was not correct. The results threw data on my dashboard sheet in places were it didn't belong.
 
Upvote 0
You must either have changed the code or put that line in the wrong place, since that line started with a .Range(...) call so it had to already be inside a With block holding a reference to a worksheet.
 
Upvote 0
here is what I have
Code: 
Sub MyCarsCombined() ' Format Col. D in worksheet "MyCars"

Dim lngLastRow As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("MyCars")
    
    Application.ScreenUpdating = False

'Uses Column A to set the 'lngLastRow' variable (find the last row).
        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

Set ws = Nothing

End Sub
This causes the reference error when the dot is in place
 
Upvote 0
VBA Code: 
Sub MyCarsCombined()                             ' Format Col. D in worksheet "MyCars"

    Dim lngLastRow As Long
    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets("MyCars")
    
    Application.ScreenUpdating = False

    'Uses Column A to set the 'lngLastRow' variable (find the last row).
    lngLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
  
    ws.Range("D2:D" & lngLastRow).Value = ws.Evaluate("=A2:A" & lngLastRow & "&"" ""&" & "B2:B" & lngLastRow & "&"" ""&" & "E2:E" & lngLastRow)

    Application.ScreenUpdating = True

    Set ws = Nothing

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Call Macro inside a form?
Replies
5
Views
473
DanteAmor
DanteAmor
D
  • Question
Workbook & File SaveCopyAs
Replies
2
Views
1K
dfolzenlogen
D
A
  • Question
VBA Avoid hard-coding values to call macros associated with values entered in a cell
2 3
Replies
21
Views
6K
ave10
A
B
  • Question
Help to simplify an emailing macro
Replies
7
Views
402
bobbybrown
B
R
  • Question
call macro based on cell value vba
Replies
3
Views
2K
rohan85
R

Forum statistics

Threads
1,216,119
Messages
6,128,946
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top