Solved

Real Time VBA

Posted on 2014-07-31
15
288 Views
Last Modified: 2014-08-01
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
0
Comment
Question by:itjockey
  • 9
  • 6
15 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40231410
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
 
LVL 8

Author Comment

by:itjockey
ID: 40231417
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40231483
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
 
LVL 8

Author Comment

by:itjockey
ID: 40231534
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
 
LVL 8

Author Comment

by:itjockey
ID: 40231843
Mr.Randy Poole,

i did what you said but there is error message.ErrorAfter ClickingThanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 40233413
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40233862
If you look at the code I posted above, your missing the global
Dim auto As Boolean
above the procedure
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Author Comment

by:itjockey
ID: 40234003
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40234018
Sure, here you go.
EE-Demo.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 40234736
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40234760
So this cell will be populated based on what is in cell j?
0
 
LVL 8

Author Comment

by:itjockey
ID: 40234827
formula in column L is too much bigger,  referencing to many cell in same row - returns to Text or "".

Thanks
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 500 total points
ID: 40234857
Give this a try
EE-Demo.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 40235127
Yeah working but range to consider only L2:L1699. it run macro even in row below L1699.  

Thanks
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 40235170
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel VBA - Public declaration of variables 3 45
Match formula returns N/A 5 25
Excel case statements 3 24
Copy all Sheet1-Sheets into a newly created workbook using VBA 8 33
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now