Real Time VBA

Hi Experts,

I need a piece of code which execute my module "Copy Trigger Data" on formula out put to text string in Range L2:L1699. Formula given in such a way that if condition is true then it will returns to text string else "".
before execute I require it passes through 2 condition.
For example say cell L3 returns to "Test" via formula.
Need to check
If time is not between 9:40 - 15:30 & M3="" then put M3="Zzz" & Exit
if time is between 9:40 - 15:30 & M3<>"Posted" then put M3="Posted" & execute "Sub Copy Trigger Data" Module.

Definitely I need  separate module for above procedures  & I don't want any human intervention. above procedure run as and when range L2:L1699 changes to Text string from "" i.e. automatic.


See Attached

Thanks
EE-Demo.xlsm
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
When you say Time is not between 9:40 and 15:30 do you mean the current time of when the cell was modified?
0
Naresh PatelTraderAuthor Commented:
Mr.Randy Poole,
When you say Time is not between 9:40 and 15:30 do you mean the current time of when the cell was modified?

Yes

Thanks
0
Randy PooleCommented:
In your module place the following code:
Dim auto As Boolean
Public Sub CheckChange(ByVal rng As Range)
    Dim xls As Worksheet, xrng As Range
    Dim row As Integer
    Dim tm As String
    If auto Then Exit Sub
    auto = True
    Set xls = Sheets("Data")
    If Not Application.Intersect(xls.Range("L2", "L1699"), rng) Is Nothing Then
        row = rng.row
        Set xrng = xls.Range("M" & row)
        tm = Time
        If tm >= #7:40:00 AM# And tm <= #3:50:00 PM# Then
            If xrng.Text <> "Posted" Then
                xrng.Value = "Posted"
                Copy_Trigger_Data
            End If
        Else
            xrng.Value = "Zzz"
        End If
    End If
    auto = False
End Sub

Open in new window

and in the VBA of your worksheet place this code:
Private Sub Worksheet_Change(ByVal Target As Range)
    CheckChange Target
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Naresh PatelTraderAuthor Commented:
Mr.Randy Poole,
Give some time to test as I am travelling. As soon as I reach my place I will get back to you.

Thank You
0
Naresh PatelTraderAuthor Commented:
Mr.Randy Poole,

i did what you said but there is error message.ErrorAfter ClickingThanks
0
Naresh PatelTraderAuthor Commented:
Mr.Randy Poole,


will you please look in to my WB. what the problem is? just change in column J range 2:13 change to 250%, so as it is referencing column for column L. Column L will return to text "Test". but nothing happen to real time check. returns to macro error.

Thanks
EE-Demo.xlsm
0
Randy PooleCommented:
If you look at the code I posted above, your missing the global
Dim auto As Boolean
above the procedure
0
Naresh PatelTraderAuthor Commented:
Mr.Mr.Randy Poole,

Request to you will pls send me WB instead as I have 10% knowledge of VBA. & I had tried possibilities but don't find successes.

Thanks
0
Randy PooleCommented:
Sure, here you go.
EE-Demo.xlsm
0
Naresh PatelTraderAuthor Commented:
Mr.Randy Poole,

yes it is working when i manually enter some thing in column L but not when formula out put to some string. is there any way out ?Last Request On This After This I Will Close This
Thanks
0
Randy PooleCommented:
So this cell will be populated based on what is in cell j?
0
Naresh PatelTraderAuthor Commented:
formula in column L is too much bigger,  referencing to many cell in same row - returns to Text or "".

Thanks
0
Randy PooleCommented:
Give this a try
EE-Demo.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naresh PatelTraderAuthor Commented:
Yeah working but range to consider only L2:L1699. it run macro even in row below L1699.  

Thanks
0
Naresh PatelTraderAuthor Commented:
Mr.Randy Poole,
Excellent .....i moved Copy Data Trigger to other sheet ...working good till now ...will check on monday on live trade ........if find any difficulties may get back to you?


Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.