+ Reply to Thread
Results 1 to 9 of 9

VBA Timestamp only updates if Target cell is manually changed?

  1. #1
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    VBA Timestamp only updates if Target cell is manually changed?

    Hi all,

    My primary worksheet (Sheet 2) contains a table that updates based on changes to the referent cells on Sheet 4.

    I want to make VBA timestamp my table on Sheet 2 anytime the total changes, i.e. when the Sheet 4 referents make Sheet 2 change.

    I am using the following code now:
    Please Login or Register  to view this content.
    The problem I'm having is that this timestamp macro only works if I *MANUALLY* edit the contents of the target cell (the 'total' cell from Sheet 2's table). So I get a timestamp if I manually change the target. But I want a timestamp automatically when the target cell is changed by changes in Sheet 4.

    Any ideas guys? Thank you!
    Last edited by arwebb0264; 02-12-2016 at 01:41 PM.

  2. #2
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: VBA Timestamp only updates if Target cell is manually changed?

    Code tag request removed.
    Last edited by jason.b75; 02-12-2016 at 02:18 PM.

  3. #3
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: VBA Timestamp only updates if Target cell is manually changed?

    I apologize. Done.

  4. #4
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: VBA Timestamp only updates if Target cell is manually changed?

    Set the target cell as the one in sheet 4 that will be changed manually, not the one in sheet 2 that is changed by the formula (you will need to enter the code into the sheet 4 module, not the sheet 2 module).

    There is no ideal way to track changes to formula in specific cells, it's easier to track the cells that cause the formula to change, although this is not always ideal.

  5. #5
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: VBA Timestamp only updates if Target cell is manually changed?

    Jason,

    Thank you for the advice - my question is how to set a target cell that is in a different worksheet from the VBA script.

    So, I entered my code into the Sheet2 VBA window, but I want to take your advice and set the target cell from Sheet 4.

    I am sorry if I am explaining poorly, but I believe I am following you.

  6. #6
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: VBA Timestamp only updates if Target cell is manually changed?

    Quote Originally Posted by jason.b75 View Post
    Set the target cell as the one in sheet 4 that will be changed manually, not the one in sheet 2 that is changed by the formula (you will need to enter the code into the sheet 4 module, not the sheet 2 module).

    There is no ideal way to track changes to formula in specific cells, it's easier to track the cells that cause the formula to change, although this is not always ideal.
    Jason - just to be more specific:

    I am asking whether I should:
    • enter the VBA code in the VBA window for Sheet 4, where the formula is calculated, and tell VBA to timestamp in Sheet 2.
    • enter the VBA code in the VBA window for Sheet 2, where I want the timestamp to appear, and tell VBA to target a cell in Sheet 4.

    Thank you. I apologize for such novice confusion.

  7. #7
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: VBA Timestamp only updates if Target cell is manually changed?

    No need to apologise, everybody has to start somewhere.

    Try this in the sheet 4 code module.

    Please Login or Register  to view this content.
    This will (should) timestamp Sheet2 A37 when Sheet4 B47 is changed.

  8. #8
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: VBA Timestamp only updates if Target cell is manually changed?

    No timestamp.

    I pasted into Sheet 4 Code window - is this the "module"? I know that I can Insert > Module in the left-hand nav pane, too.

  9. #9
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: VBA Timestamp only updates if Target cell is manually changed?

    That is the correct module, modules that you add by using Insert > Module don't work with events (code that runs automatically when a specific action is taken).

    Possibly events have been disabled, to re-enable them,

    In the code editor, press Ctrl g

    Then in the panel that opens at the bottom, enter the following line.

    Please Login or Register  to view this content.
    and press enter.

+ 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. Replies: 15
    Last Post: 08-21-2015, 03:43 PM
  2. Code works when cell is manually changed only the first time and not with vba
    By sdl2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-17-2014, 08:18 PM
  3. [SOLVED] I Want the Timestamp Value Not to Change if the Name Cell Changed
    By bssol in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-07-2013, 04:48 AM
  4. VBA to execute when target cell value changed/filled
    By Kehjz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-03-2013, 09:18 PM
  5. [SOLVED] Worksheet_Change(ByVal Target As Range) - set to only update when 3 target cells changed?
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 06:40 PM
  6. static timestamp that updates using vba code
    By xenador in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-27-2007, 02:56 AM
  7. manually control link updates sourced through AtlasXL.xla
    By Axapta User in forum Excel General
    Replies: 0
    Last Post: 08-21-2006, 11:20 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