+ Reply to Thread
Results 1 to 7 of 7

Writing new record in a table upon the update of a cell or cell range

  1. #1
    Registered User
    Join Date
    03-05-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Writing new record in a table upon the update of a cell or cell range

    I receive automatic updates of a range of cells from an outside source (stock market trades). To accomplish this each cell has a formula in it like for example =RTD("esrtd",,"XLI","Volume"). How can I write a new record in a table every time I receive an update, basically creating a log of market activity? To simplify the question:

    How can I make the code below run when the value in A1 is updated form the outside source? (It works if I manually type something in A1, but does not work when A1 is updated via a formula).

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
    'YOUR CODE HERE
    End If
    End Sub

    Assumes A1 is the cell
    Last edited by bpopov007; 03-05-2014 at 07:27 PM.

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Writing new record in a table upon the update of a cell or cell range

    Welcome to the forum bpopov007,

    Can you try the following? I have used the Calculate event instead of the Change Event.

    It might solve your problem! NOTE: It assumes that A1 is the active cell (selected cell).


    Please Login or Register  to view this content.
    Please let me know how you get on.

    Regards,

    David


    When you reply please make it clear WHO you are responding to by mentioning their name.

    If this has been of assistance, please advise. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".
    Last edited by David A Coop; 03-06-2014 at 01:35 AM.

  3. #3
    Registered User
    Join Date
    03-05-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Writing new record in a table upon the update of a cell or cell range

    Thanks,

    My final code:

    Private Sub Worksheet_Calculate()
    If ActiveCell.Address = "$B$2" Then

    Range("B4:H4").Select
    Selection.Copy
    Range("B7:H7").Select
    Selection.Insert Shift:=xlDown

    End If
    End Sub

  4. #4
    Registered User
    Join Date
    03-05-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Writing new record in a table upon the update of a cell or cell range

    David,

    Is there a way to have the same action but if A1 is NOT the active cell? To give you an example, if A1 contains a formula "=B1", then if we type something in B1 and press tab, A1 will change, but will not be 'active'.

    Thanks!!

  5. #5
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Writing new record in a table upon the update of a cell or cell range

    Hi bpopov007,

    Sorry, I'm not clear as to what you're asking!

    Let me explain what the macro is doing, it might help you answer your own question.

    We are using an event macro. This means that every time something happens in excel (an event) a macro can be triggered. In this case, we are saying "every time the workbook calculates - do something".

    We are narrowing this down by saying If ActiveCell.Address = "$B$2" Then - this restricts the number of times the "do something" occurs, and assumes you have B2 selected, and any changes are made ON ANOTHER SHEET.

    If you want to trigger the macro when you enter something into B1, you need a different event macro. A Worksheet_Change macro - something like this.

    Please Login or Register  to view this content.
    Regards,

    David


    When you reply please make it clear WHO you are responding to by mentioning their name.

    If this has been of assistance, please advise. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

  6. #6
    Registered User
    Join Date
    03-05-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Writing new record in a table upon the update of a cell or cell range

    David,

    I understand completely what you wrote, tried the code and it works as you describe. However, let me clarify what I am looking for: I receive audomatic real time data from a web source. For simplicity lets assume that data updates cell A1 from time to time. Inside cell A1 currently ther is a formula like =ESQuote($A2,"Volume") and every tme the web source updates the data, the cell shows a different number. I have already code, which would copy the data from A1 and paste it in another cell as a number. All I am trying to do it automate the process. I need the copy and paste code (which I already have) to be triggered by the updated value in A1. The event macros you suggested do not recognize the update in cell A1 and do not trigger my copy/paste code. Apparently the event of updating from a web source does not constitute a 'change' nor 'calculation', but I do not know what it is. In my previous post I tried to simplify the question, because I noticed that similiar to the web feed update is a situation when, for example you write something in A1 and you have A9 = A1; A9 will always change to equal A1, but if you put your macro triggering event on A9, and if you type something in A1 and then press 'tab', your macro will not recognize the event.

  7. #7
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Writing new record in a table upon the update of a cell or cell range

    This is outside my experience.

    However, Google Excel - Execute Code After A Data Connection Refresh Is Finished and you might find the answer you need.

    David

    When you reply please make it clear WHO you are responding to by mentioning their name.

    If this has been of assistance, please advise. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Writing data to specific cell in a table
    By teacher_rob in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-14-2011, 09:10 AM
  2. Replies: 1
    Last Post: 02-19-2009, 03:00 AM
  3. [SOLVED] Record update date in cell
    By Phil Hageman in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-26-2006, 09:30 PM
  4. Writing update date and time in a cell on inputs
    By a94andwi in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-17-2006, 09:45 AM
  5. [SOLVED] RE: Record update date in cell
    By Patrick Molloy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-06-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1