+ Reply to Thread
Results 1 to 10 of 10

RuN VBA process when cell value changes

  1. 04-06-2016, 02:31 PM #1
    snuffnchess
    snuffnchess is offline
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    RuN VBA process when cell value changes

    I have a VBA running that references an Access DB Table everytime i click a button called "Calculate"

    Is there a way to make the process run WITHOUT hitting calculate.... say any time i change the value of cell (Z12)? Below is a copy of the script that runs when "calculate" is clicked.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 04-06-2016, 02:34 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: RuN VBA process when cell value changes

    You should be able to use a Worksheet Change event handler to monitor one or more cells and execute the macro(s) when the cell value(s) change.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 04-06-2016, 02:57 PM #3
    snuffnchess
    snuffnchess is offline
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Re: RuN VBA process when cell value changes

    Is doing something like this what you mean??

    I was not able to get it to work when changing Z12 I saved it in a separate module from "main2"... should it be in the same one??
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 04-06-2016, 04:08 PM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: RuN VBA process when cell value changes

    The comparison is case sensitive.

    Use this:

    Please Login or Register  to view this content.
    or this:

    Please Login or Register  to view this content.

    You don't need "Call".
    Reply With QuoteRegister To Reply
  5. 04-06-2016, 04:16 PM #5
    snuffnchess
    snuffnchess is offline
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Re: RuN VBA process when cell value changes

    I tried both, and neither worked.....
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 04-06-2016, 04:38 PM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: RuN VBA process when cell value changes

    Right click on the Data Entry worksheet tab; select View Code; in the window that is displayed, paste the Worksheet Change event handler code. The code MUST go in the worksheet class module, not a standard module.

    That should work.
    Reply With QuoteRegister To Reply
  7. 04-06-2016, 05:26 PM #7
    snuffnchess
    snuffnchess is offline
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Re: RuN VBA process when cell value changes

    Just tried and it still did not work.... Does it matter that Z12 is a drop down?
    Attached Files Attached Files
    Last edited by snuffnchess; 04-06-2016 at 05:39 PM.
    Reply With QuoteRegister To Reply
  8. 04-06-2016, 06:27 PM #8
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: RuN VBA process when cell value changes

    The drop-down is not a problem; it works just fine. I can't fully test your code because I don't have the files.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 04-06-2016, 07:20 PM #9
    snuffnchess
    snuffnchess is offline
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Re: RuN VBA process when cell value changes

    I closed Excel and reopened, and then it started working. Thank you so much for your help.
    Reply With QuoteRegister To Reply
  10. 04-06-2016, 07:27 PM #10
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: RuN VBA process when cell value changes

    You're welcome. Thanks for the rep.

    That probably means .EnableEvents was set to False. Easily happens if code crashes without closing down tidily.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] For Each cell In Range.SpecialCells(xlCellTypeBlanks) doesn't process every cell.
    By TMS in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-05-2015, 12:36 PM
  2. vba to process file based on cell value
    By lynnsong986 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-25-2014, 04:11 PM
  3. Copy cell value to new cell and exclude all spaces in process
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-28-2011, 03:42 PM
  4. Match two cell values and then do process
    By jamer02 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-27-2011, 09:00 AM
  5. Speed up cell deleting process
    By carddards in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2008, 12:29 AM
  6. [SOLVED] How to count process running time ( process not finished)
    By miao jie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2005, 06:06 AM
  7. [SOLVED] How to count process running time ( process not finished)
    By miao jie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-12-2005, 02:06 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1