Call VBA code depending on cell value

markb9

New Member
Joined
Mar 20, 2009
Messages
8
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Mar 20, 2009
Messages
8
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

Legend
Joined
Feb 18, 2002
Messages
13,690
Run a WorkSheet_Change Event on the cell the person is typing in a Number
Say on Sheet2, G1
Code:
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

New Member
Joined
Mar 20, 2009
Messages
8
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

Legend
Joined
Feb 18, 2002
Messages
13,690
I don't work with email codes, but I guess it would be
Code:
.Body = Range("$F$2")
or perhaps
Code:
Dim myText as String
myText = Range("$F$2")
'''' code
.Body = myText
lenze
 

Forum statistics

Threads
1,082,128
Messages
5,363,325
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top