Change Event – Excel 2003

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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_platforms/metatrader4.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.

Reply | Quote

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

Its a start anyway.

Have Fun

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Andrew

Thank you for the briliant work.

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Rory

Thank you for the advice.

A few questions on this since I have never used it before: Can you copy 6-8 columns of information in a record that will span a few thousand records to be analysed? Can you set intervals for the recording of information? When you talk about servers are you refering to the server I connect to or to the data suppliers server. Where I am I have to connect via a wireless connection or a mobile modem to a service provider in order to get to the info I am after. I would appreciate a bit more detail on this since I have no experience with this.

Thank you

Reply | Quote

You can copy whatever you like!
I was referring to the data supplier’s server. If you have thousands of cells all returning data from this external source, then my suggestion won’t work – it’s intended for a few cells. By linking the control to the cell you do not need to use timers – the change in the cell value triggers the control’s change event, which is what you use to run your copying routine.

Reply | Quote

Rory

Thank you for the reply.

I want to copy about 10 cells at a time. Can you explain briefly how you set this up, I have never done it and do not know how or where to set it up.

I understand what you say about using this to trigger the copy routine, but will it be able to copy at intervals and not every change. I can use the previous procedure (calculation event on the worksheet) that I posted in the APIclock workbook WITHOUT the clock to copy every single change without a timer. The problem is that the data I want to copy can change 20 times a second and I do not want to copy all that. I want to copy at set intervals to reduce the quantity of data copied.

If your suggestion can not work here it will be a drawback, but if the only problem with the clock I posted will be the Undo button then I can live with that since I use more than one computer and I can use one where I hardly use Excel or Word on to accomplish this.

My problem is that I do not know how to inpcorporate the code in the APIclock workbook with the code that Andrew wrote, unfortuneately my knowledge as far as VBA code is concerned is a bit limited or if compared you, Andrew, Hans, etc, then it will fall into the category of Severely Limited.

Reply | Quote