My macro wont run automatically

[Goody61865]

I can initiate in manual and it works just fine but I want it to run automatically when a cell value is entered greater than zero is entered.

Here is my code:

Option Explicit
Sub Worksheet_calculate1()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double
Dim FormulaCell As Range


NotSentMsg = "Not Sent"
SentMsg = "Sent"
MyLimit = 0
Set FormulaRange = Me.Range("D56")
On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
Else
If .Value > MyLimit Then
MyMsg = SentMsg
If .Offset(0, 1).Value = NotSentMsg Then
Call Mail_with_outlook5
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 1).Value = MyMsg
Application.EnableEvents = True
End With
Next FormulaCell
ExitMacro:
Exit Sub
EndMacro:
Application.EnableEvents = True
MsgBox "Some Error occurred." _
& vbLf & Err.Number _
& vbLf & Err.Description
End Sub


I am sure it is something simple that I am missing. Thanks for the help

 

[rs2k]

Could you drop it in a select case statement?

Private Sub Worksheet_Calculate()
Select Case Range("A1").Value  'replace A1 with your cell
    Case Is < 0
    'your code
    Case Else
End Select
End Sub

HTH
Colin

 

[VoG]

You can't invent your own event procedures. It should be

Sub Worksheet_Calculate()

 

[Goody61865]

Sorry, I added 1 after calculate after nothing else would work to differntiate it from the other sheet with the same code for a different result. I forgot to remove it, but I still have the same problem. It only initiates when I hit the "run" button. The other codes work just how they are supposed to.

 

[VoG]

Where did you put the code? You need to right click the sheet tab, select View Code and paste in the code (with the 1 removed).

 

[Goody61865]

That is where it is.

 

[VoG]

Make sure that Events are enabled. In the code window press CTRL + G then in the Immediate window type

Application.EnableEvents=True

then press Enter.

 

[Goody61865]

No change, I have the exact same code in another worksheet and it works just fine.

 

[Goody61865]

Maybe I am trying to be to complicated? Should I just use a simple formula if the cell value is greater than zero initiate macro?

 

[VoG]

To check if the code is actually firing, add this near the top of the sub

MsgBox "Boo!"