Call VBA code depending on cell value

[markb9]

Hi,

I am having problems with creating a private sub that runs a sub that i have already created that works.

Here's the example, if the value in cell A1 changes to below a specified amount it will run my macro named macro1.

The macro shopuld only run once though, until the value changes again.

 

[lenze]

First, you need to post your code?? Also, how does A1 Change. By Formula? Input?Data import? If by formula, what is the formula?

lenze

 

[markb9]

The sub i would like the private sub to run is:

Sub Mail_ActiveSheet()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
With Destwb
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = "xyz@123.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Jason/Ben, Test e-mail. Regards, Mark"
.Attachments.Add Destwb.FullName
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Cell A1 will be linked to another sheet in the workbook that is updated once a day by someone just typing a number in to the linked cell.

 

[lenze]

Run a WorkSheet_Change Event on the cell the person is typing in a Number
Say on Sheet2, G1

If Target.Address = "$G$1" Then
'Check value of Sheet1:A1 and save to sheet2 (or somewhere)
'If value is below limit and different from the value saved on sheet2, Call myMacro
End If

lenze

 

[markb9]

Thats great, thanks for the help.

Just one more query regarding the code i was using to send the e-mail earlier.

Is there a way when stating the e-mail body text to change that to text in a range of cells in the active sheet?

So the e-mail sent would read whatever text was held in cell F2.

Thanks in advance

 

[lenze]

I don't work with email codes, but I guess it would be

.Body = Range("$F$2")

or perhaps

Dim myText as String
myText = Range("$F$2")
'''' code
.Body = myText

lenze