Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    HI

    I want to solve the following problem. I want numbers plus time & date stamps to automatically add to a record sheet. I am not sure if this is a change event procedure or something else. Plus I do not know what code to use or where to add the proposed code to achieve this task.

    I would appreciate any help or guidance on this

    I attached a small Workbook to illustrate.

    Thank you very much
    Attached Files Attached Files
    Regards
    Kobus

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The change event of the specific worksheet would do it.
    You need to check the Target Parameter cell is "A2"

    The code below as the On Change Event of the Input Worksheet does the job
    I've added a couple of checks to make sure it also only runs for Numeric Entries in A2

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim strSheet As String
    Dim rng As Range
    
    'Sheet to Write (variable allows easier change later)
    strSheet = "Record"
    
    'Check if Target Cell was A2 and NOT Blank
    If Target.Address(False, False) = "A2" And Target <> "" Then
        If IsNumeric(Target) Then
            'Determine Last Cell
            If Sheets(strSheet).Range("A3") = "" Then
                Set rng = Sheets(strSheet).Range("A3")
            Else
                'Next NON Blank Cell
                Set rng = Sheets(strSheet).Range("A2").End(xlDown).Offset(1, 0)
            End If
            'Place Values and Formats
            rng = Target
            rng.NumberFormat = "0.00"
            rng.Offset(0, 1) = Date
            rng.Offset(0, 1).NumberFormat = "d/mmm/yy"
            rng.Offset(0, 2) = Now - Date
            rng.Offset(0, 2).NumberFormat = "hh:mm:ss"
        End If
    End If
    
    End Sub
    I have attached your example as well (zipped) with the code in the module

    [attachment=87553:Change Event.zip]
    Attached Files Attached Files
    Andrew

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Andrew

    This works perfectly, thank you very much.

    This brings up another question, this code obviously work since you press the Enter key every time that you enter data, how can you make it work for a different application like linking cell A2 to steaming data like stock prices etc. Is it possible for instance to make streaming data work as if you have entered it or can code be added that "force" an entry say every second or every 5 seconds?

    I would appreciate your opinion and thoughts
    Regards
    Kobus

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Kobus View Post
    Hi Andrew

    This works perfectly, thank you very much.

    This brings up another question, this code obviously work since you press the Enter key every time that you enter data, how can you make it work for a different application like linking cell A2 to steaming data like stock prices etc. Is it possible for instance to make streaming data work as if you have entered it or can code be added that "force" an entry say every second or every 5 seconds?

    I would appreciate your opinion and thoughts
    That is rather harder.
    You can trigger code by a variety of methods, not just worksheet and workbook events
    One danger of timer, other than the need to have a hidden form or timer control running in the background is
    needing to determine if the value has actually changed so this would need to be factored in to any solution.
    If you are linking, where would the data be coming from?
    It is necessary to know this before looking at the best solution.
    Andrew

  5. #5
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew

    Here is a link to an application that can be down loaded, once the application is open the following setting must be enabled:

    http://www.alpari.co.uk/en/trading_p...tatrader4.html

    Tools > Options > Server then tick Enable DDE Server

    Once this is done then open the Excel Workbook: Click enable Macro's and click Update links.

    I attach a workbook with a link in Cell A2

    You will see the cell changing as the Data comes in.

    It is not necessary to capture every change, you can set a timer for say example: every 10 seconds or 30 seconds or 60 seconds.

    Hope this is enough information.
    Attached Files Attached Files
    Regards
    Kobus

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have a look at this example.
    You need to change the A2 and A3 formulas back to your references.
    I have just put static values in them

    Also have a look at this site for more information on timers

    http://www.cpearson.com/excel/OnTime.aspx

    [attachment=87561:Change Event3.zip]


    Its a start anyway.

    Have Fun
    Attached Files Attached Files
    Andrew

  7. #7
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew

    Good morning.

    Thank you very much, it works brilliantly, I will study the information on the site you suggested.

    This is great work.

    Have a nice day.
    Regards
    Kobus

  8. #8
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew

    I would be greatfull if I can tap your brain once more. I am trying to copy more data as per the attached workbook.
    I now have 4 pieces of data that I want to copy but I only manage to copy the first and the fourth, do you mind checking what I did wrong?
    I would appreciate it very much.

    I read the piece as you suggested, very interesting. I found that while you are running only this workbook there are no errors, the problems only start when you try to work on other workbooks in the same time. So for now I will run the workbook on its own on a separate computer.
    Attached Files Attached Files
    Regards
    Kobus

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Yes I had originally allowed for 2 sets of data so was using the Initial
    Cell Reference plus Offset (0,1) for the second
    Then Offset (0,2) and (0,3) for the date and time

    With 4 values you need to have lines for the data

    First cell, then Offset (0,1) (0,2) (0,3)
    Then use (0,4) and (0,5) for date and time.

    You had adjusted the Date and Time, AND the 4th reading,
    BUT ommitted the extra lines for the 2nd and 3rd

    See attached example

    [attachment=87583:ForexData.zip]
    Attached Files Attached Files
    Andrew

  10. #10
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew

    Thank you for the good lesson, I learned a lot and the file works great.

    I want to try a few more enhancements, if I can not figure out the referencing I might call on your supperior knowledge again.

    So far the code works 100%, just a question: how do you stop the opperation and just save the workbook? So far I save it and when I close it then it boots automatically again. For now I have to force a workbook crash in order to get it to close.
    Regards
    Kobus

  11. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    That's was a good question and I can at the moment only come up with the attached solution
    Whilst using the Application.OnTime Timer Event anyway.

    In the attached example I have placed a STOP and RESTART Timer Button on the Collection Sheet.
    Before Closing the file you must Press this STOP button.

    I had to add a modification to the Code to check for the Public Variable fStop being set to True.
    I also had to add a StopTimer Macro

    It is the last Argument of the OnTime that turns it on and Off
    If the Last Argument is True then the Timer is Restarted
    If it is False then it stops after the next run

    Best I can do unless anyone else can come up with a solution.
    I was going to use the BeforeClose Event, but it would not execute the StopTimer event whiuch is why I had to add the button

    I also added a Restart Timer button as an additional option.

    You could if you wanted scrap the On Open event of the workbook and just have the
    timed capture start and stop with the buttons.

    [attachment=87584:ForexDataStartStop.zip]
    Attached Files Attached Files
    Andrew

  12. #12
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew

    Thank you for the briliant work.

    I will study what you have done, I am always impress to see a master at work.
    Regards
    Kobus

  13. #13
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Andrew

    I found a new clock that I posted over the weekend in another post. The clock does not crash the system or stop running when you change workbooks, etc.

    More importantly I found another script that execute code when a formula result change.

    Have a look at the attached workbook, you will see that the results on the left, the yellow cells get copied to the green cells everytime the clock in A5 change.

    Maybe we can use similar code instead of the Change Event

    One other question: in the case where we specify intervals of say 10 seconds is it possible to have it print at round numbers like 10 sec, 20 sec etc or in the case of say 15sec intervals, 15, 30, 45, etc or in the case of minutes to print at 1:00 or 3:00 (mm:ss) etc. I do not know how much work is involved in enhancing the code but I would appreciate it if it is possible without to much hassle.

    I will send another workbook after this one to show you some results of the original workbook that you coded.

    Thank you again for the great work I really appreciate it.
    Attached Files Attached Files
    Regards
    Kobus

  14. #14
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Andrew

    Here is the workbook with the results and with some additions and changes I made.

    If you look on the Record sheet you will notice that records are printed and then a few seconds later it print again. This is on 1min intervals, any reason for that?

    It does not happen all the time, so I was wondering why it is doing that.

    I would appreciate it if you can make any changes or enhancements to do it to the attached workbook. I do not take it for granted that you are going to spend more time on this but if you are going to I would much appreciated it. I can not tell you how much I learn here from you, Hans, Steve, etc, etc. I can assure you I do not take you guys for granted and do really appreciate the guidance and education here in the Lounge.
    Attached Files Attached Files
    Regards
    Kobus

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you are using a DDE or RTD server, then what you can do is link a textbox from the Control Toolbox to the result cell and then use its Change event to log changes to the cell value. More efficient than timers.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •