Naresh Patel
asked on
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
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
When you say Time is not between 9:40 and 15:30 do you mean the current time of when the cell was modified?
ASKER
Mr.Randy Poole,
Yes
Thanks
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
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
and in the VBA of your worksheet place this code:Private Sub Worksheet_Change(ByVal Target As Range)
CheckChange Target
End Sub
ASKER
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
Give some time to test as I am travelling. As soon as I reach my place I will get back to you.
Thank You
ASKER
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
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
If you look at the code I posted above, your missing the global
Dim auto As Boolean
above the procedure
Dim auto As Boolean
above the procedure
ASKER
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
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
Sure, here you go.
EE-Demo.xlsm
EE-Demo.xlsm
ASKER
So this cell will be populated based on what is in cell j?
ASKER
formula in column L is too much bigger, referencing to many cell in same row - returns to Text or "".
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah working but range to consider only L2:L1699. it run macro even in row below L1699.
Thanks
Thanks
ASKER
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
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