+ Reply to Thread
Results 1 to 4 of 4

Cell changes that trigger subroutines

  1. #1
    Registered User
    Join Date
    01-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Cell changes that trigger subroutines

    Hello,

    I am looking to have a change in value of a cell trigger some range cut and pasting. The change in the cell will be driven by the calculation tree (ie it will not be a manual change)

    However, Excel does not allow subroutines to be called from cells, so I'm not sure how to achieve this.

    Your help is much appreciated

  2. #2
    Registered User
    Join Date
    01-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Cell changes that trigger subroutines

    I'll add that I've managed to hack this by highjacking the SheetCalculate event and checking there if the value of the cell has changed to a value that warrants the cut and paste action, but I'm hoping for a solution without call overhead every time the sheet caculates. (My sheet receives live data and hence can recalculate many many times per second...)

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Cell changes that trigger subroutines

    Hi,

    One way might be to set the Calculation mode to manual and then have the following code in the Worksheet_Change event

    Please Login or Register  to view this content.
    You could put Application.Calculation=xlCalculationAutomatic in the Deactivate event of the worksheet, (and perhaps the xlCalculationManual in the Activate event), to avoid leaving the calculation set to manual

    HTH

  4. #4
    Registered User
    Join Date
    01-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Cell changes that trigger subroutines

    Thanks Richard, I like your thinking but this solution is very similar to the hack I've already done. Both solutions have to check the state of the cell in question on every change to the sheet.
    The problem is that there are potentially thousands of sheet changes per second that won't require the action so checking every time if the action is required significantly reduces the data volume I can pass through the sheet.

    I am hoping to somehow latch onto the cell in question changing and sit still doing absolutely nothing untill it does change.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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