+ Reply to Thread
Results 1 to 12 of 12

How can we trigger a simple macro based on cell value of another cell automatically with

  1. #1
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    How can we trigger a simple macro based on cell value of another cell automatically with

    a code and without any keyboard short-cut. This simple macro is to copy a1 to b1
    How to write a code which will directly trigger it if C1=1 ?
    There is a formula in C1 = if(or(a1<-7,a1>7),1,0)
    Please Login or Register  to view this content.
    Last edited by omega0010; 08-15-2013 at 05:38 AM. Reason: A formula has been added

  2. #2
    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,087

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    Does C1 accept keyed input or does it have a formula in it?


    Regards, TMS
    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


  3. #3
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    There is a formula in C1 = if(or(a1<-7,a1>7),1,0)
    Regards
    Omega0010

  4. #4
    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,087

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    Please Login or Register  to view this content.

    Regards, TMS

  5. #5
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    Hey TMS !
    Thanks a lot ! Your kind help and time is highly appreciated
    God Bless !
    Omega0010

  6. #6
    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,087

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    You're welcome.

  7. #7
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    I wanted to run another macro with it( the original C1 cell has been changed to G8). I tried to put it in continuation but it does not work. Can you please tell me how to add other macros including this one referring to G11 ( which again is going to be a formula cell yeilding 1, G11=IF(OR(E2<-4,E2>4),1,"") )and the Macro name is QMaster ( instead of w_TMS )

    Please Login or Register  to view this content.

  8. #8
    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,087

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    If I am testing one cell/range, I usually escape from the routine as soon as one of the conditions fails.

    So, too many cells changed, exit. Not a cell of interest, exit. Not the value I'm looking for, exit.

    If you want to monitor more than one cell/range, you need to take positive action for each one and exit after you have completed the required actions.

    So, after excluding multiple cell changes, we need to check that it is a cell of interest and it is the value being checked for.


    Untested, but try:

    Please Login or Register  to view this content.

    Regards, TMS

  9. #9
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    Well I tried .......... The original macro still works but the new one QMaster does not !

  10. #10
    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,087

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    I can't really help you without knowing what your code does ...

    This works for me. Note that it sets up formula in cells G8 and G11 for testing purposes. You need to remove that code, or comment it out.

    Please Login or Register  to view this content.

    Regards, TMS

  11. #11
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    Thanks a lot to you for your time and kind help... I have tried this code .... but somehow two sets do not work out.....
    O yes there was one mistake in your code which made it sluggish... I just tried to use logic and corrected it ....
    "' more than one cell changed?
    If Target.Cells.Count > 1 Then Exit Sub"

    Should be < 1. This made the code ( original one was the best ) extremely fast !
    I really appreciate all your help ! You are a patient and helpful person.
    Thanks again

  12. #12
    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,087

    Re: How can we trigger a simple macro based on cell value of another cell automatically wi

    Target.Cells.Count will never be less than one. This is a Change event, therefore, at least one cell must have changed. The point is that, if Target.Cells.Count > 1, then several cells have been changed. This could be because all the data in a row or column has been deleted. The code is not designed to cater for that (you'd need a loop) but, in this case, it is only interested in changes to any of the cells that affect the formulae in cells G8 or G11. So you test the precedents for changes but look at the value in the cell.

    Changing the test to < 1 will mean it is hellishly fast ... primarily because it won't be doing anything.

    You are a patient and helpful person.
    No, I'm not. I'm trying to be both but you are making it difficult. You started by asking for something that bore no relationship to what you actually want/need. Then it became two cells, neither of which was checked originally, and then you introduce routines that don't do what you want but which you have not shared with us.

    If you wish to have this problem addressed, please post a sample workbook with some typical data and all the code you are trying to automate.

    TMS

+ 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. simple macro to hide or unhide rows based on a cell value
    By Russ Fuquay in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-19-2013, 06:32 PM
  2. Automatically send an email based on the value of a cell and/or trigger date
    By psn.arya in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2013, 06:17 PM
  3. Automatically Trigger Event when Cell Changes
    By Thinkslow in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2013, 03:34 AM
  4. Macro to automatically update sheet name based on cell value
    By seanfoxen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2012, 05:17 PM
  5. Trigger a macro based on cell colour change
    By hermithead in forum Excel General
    Replies: 1
    Last Post: 02-23-2010, 09:57 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1