asked on

eMail VBA

Hi Experts,

Let me 1st explain what I have in this WB.
There is Three Sheets in WB EE-Demo-1 i.e. Data – Recorder – LogFile
Data sheet have real time fields – our concern is Column A & Column M. there is complex formula in these two columns with reference to many column.(For Simplification I had changed In attached) if condition matched then in these two column (A & M) returns to String Values. Column A Returns to this Fix strings – Level 1 – Level 2 – Level 3 – Level 4 – ID 1 – ID 2 & Column M returns to variable strings.

In attached there is one module called posting which post this triggers (Column A & M) in sheet Recorder & LogFile .Rules are for posting in Recorder is - For any given Ticker value (Column B in the Data sheet), supersede an existing row with that Ticker value if a higher Level value exists. For example:
If a row with Ticker = "A" and Level = "Level 1" already exists on the Recorder sheet and a same ticker value in
Data sheet has change to Level = "Level 3", overwrite the existing row on the Recorder sheet.
This rule applies independently for Level = "Level x" or Level = "ID x"; neither will overwrite the other.
The net effect should be that, for any given Ticker value, only one (1) "Level x" and/or one (1) "ID x" value should exist and it should be the highest value from the Data sheet.

In LogFile it registers all triggers which are posted in Recorders sheet in separate row (no over pasting for Levels & IDs). In this sheet there is only two row data posted i.e. data Time & Column M String value.

Now question part I want to run this above mention Sub Copy Trigger Data on any change happen on column A on auto basis. Irrespective active WB is different. & there are 3 check boxes in sheet Recorder – I need if any or all checked - it performed accordingly for sheet logfile – Say if I had clicked Audio then any new change in sheet LogFile it sounds. If message checked then – pop message box appear on any change happen in Sheet logfile with message Column A & B values or if I checked eMail then email alert & subject line is – Date Time (column A) & String Value(Column B) from sheet logfile.

I had tried specific sec & run above code but frizz my real time data in sheet Data.
Why I asked you specifically – as Sir your solution for Column Value email working perfectly with my real time update addin. No conflict between your code & my addin.

Looking from you a solution with highest hopes & I will be very thankful to you.
EE-Demo-1.xlsm

ASKER CERTIFIED SOLUTION

Robberbaron (robr)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naresh Patel

ASKER

Sir,
Give me some time to respond you. as I am on my way back to home(traveling).

Thank You

Naresh Patel

ASKER

Sir,

1) Nope none of the cells manually changed, it is updated by Add in installed in my machine.
2) For example if you put any numbers (1 to 6) in range L8:L13 & run Sub Copy Trigger Data - you will see entries in Sheet Recorder As well as in sheet LogFile. i want all checked option do as its name when there is any new entries in logfile.so if you put in numbers in L8:L13 there will be 8 send by trigger & subject line is - Logfile Column A & B

Thanks

Naresh Patel

ASKER

Sir.Robberbaron (robr),

let split this question in 2 part - requesting solution for 1st part here & for 2nd part I will ask other question.

Part 1 - let make it simpler - in attached WB - sheet Recorder - Range - A3:F3 - any change then auto run Sub Copy Trigger Data.

Thanks
EE-Demo-1.xlsm

Robberbaron (robr)

as said before, i dont think there is a way to trigger the macro if a formula changes. All of A3-> F3 are formula....

triggering on Worksheet Calculate is likely to give results you dont want.

eg

Private Sub Worksheet_Calculate()
    'Copy_Trigger_Data   'gives extra results even on no change
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 3 Then
       'this never gets fired even though the data value changes.
        Copy_Trigger_Data

    End If
End Sub

Open in new window

Naresh Patel

ASKER

Sir.Robberbaron (robr),

Apology for delay in reply .I am facing some heath issues ...surly I will revert back to you tomorrow.

Thanks

Naresh Patel

ASKER

Sir.Robberbaron (robr),

Sorry for delay in reply.
As you mentioned it is not possible to execute code on formula changes then I come up with different approach.New File Attached with modification.
which is same as "Column Value Email" so if any change (i.e. <> "") in Sheet Data - Column L then it post entry in sheet Recorder, there is 1800 row in sheet data so I need code post data in sheet recorder once per row in a day so say if row 3 triggered once then onwards on next trigger it should be ignored. And postings happen only between time 9:45 to 15:30.

How posting data in sheet Recorder.(See attached)
in attached EE Demo WB – sheet Recorder -
Column A= Date of Trigger
Column B = Time of Trigger
Column C = Column E (Past Special Value)
Column D:E = Formula which hard coded in Code itself
Column F:G = Column J:K (Past Special Values)
Column H = Column Z (Past Special Values)
Column J:M = Column A:D (Past Special Values)
Column N:AN = Formula which hard coded in Code itself

Format hard coded in Code.
For numeric values two decimals.
For % two decimals
For Criteria 1 & Criteria 2 - % Format is no decimals.

Description looks too bigger but if you open attached WB I had gave back ground colors for easy understanding. Yellow background color is for copy past special as values from sheet Data to Recorder & Blue back ground for hard coded formulas.
One next day opening of WB it auto clear all sends. (as in your column value email you added one column for to find it is mailed or not, so I assume that you may also add one column in sheet Data too)

Thanks You

Naresh Patel

ASKER

Are You With Me Sir.?

Naresh Patel

ASKER

Are you with me Sir?

Naresh Patel

ASKER

Robberbaron (robr)

i dont see a new workbook.

what code have you tried yourself ? This seems to be getting to big to help you with.
How are you going to tell if a row has been triggered already ?

Naresh Patel

ASKER

Here is the WB....sorry I forgot to attached. There are two WB one is EE Demo.xlsm in which I want code do above process. other is Send-Mail-V5.xlsm, which is the same one which you had been created & It perform on any buy or sell triggers - sends mail . I need instead of sending mail it copy and past data to sheet - recorder in WB - EE Demo from sheet Data WB EE Demo. there is orientation is different in both WB in Send mail WB code execute on event of any change in column K but in EE Demo WB column is M.

there is nothing to do with WB Send mail, all I need to do with WB EE Demo. SendMail WB attached just for understanding that in column K any triggers then it sends mail but in EE Demo I need any triggers in sheet data it post the above mention entries in sheet recorder.

Comments is modified .
Thanks
Send-mail-V5.xlsm
EE-Demo.xlsm

Naresh Patel

ASKER

Sir.Robberbaron (robr),

Just to simplify New WB EE Demo.xlsm - Only One sheet "Data".

Now posting VBA Code not required as in attached WB Module Copy trigger data is added.
Now need is executing this Code module on criteria match on range L2:L1699 i.e. <>””

Flow like this
Criteria match on column L Range L2:L1699 i.e. <>””
Check for Time – is between 9:45 -15:30 & Column M related row is <>”Posted”
If Time is between then & related row column M is <>”Posted” then Execute Code Module “Copy Trigger Data” & Mention “Posted” in column M
If Trigger is not between Time then don’t execute .
code runs in Real time bases.

Thanks
EE-Demo.xlsm