+ Reply to Thread
Results 1 to 7 of 7

Macro to run when cell value changed via FORMULA (not human intervention)

  1. 09-05-2017, 06:46 PM #1
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Macro to run when cell value changed via FORMULA (not human intervention)

    Hello,

    I have a named range in cell sheet1 cell A1 named 'UserSelectCategory'.

    In the 'ThisWorkbook', I have a macro that captures what is the oldString value.

    Please Login or Register  to view this content.
    In the 'sheet1', I have the following code, but giving me the error.

    Please Login or Register  to view this content.
    The error message that I get is

    Compile error:
    Procedure declaration does not match description of event or procedure having the same name.

    May I know how to fix this issue?

    What I want to do is basically, when the cell value change via formula, I'd like to change the filter value of PivotTable3, where the pivot table is located in a different sheet, named 'Selection'.
    Reply With QuoteRegister To Reply
  2. 09-05-2017, 07:22 PM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro to run when cell value changed via FORMULA (not human intervention)

    To fix that error change this,
    Please Login or Register  to view this content.
    to this.
    Please Login or Register  to view this content.
    However you will now get an error here.
    Please Login or Register  to view this content.
    as Target is not defined.

    What formula do you have in C12?
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 09-05-2017, 08:34 PM #3
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Macro to run when cell value changed via FORMULA (not human intervention)

    Quote Originally Posted by Norie View Post
    To fix that error change this,
    Please Login or Register  to view this content.
    to this.
    Please Login or Register  to view this content.
    However you will now get an error here.
    Please Login or Register  to view this content.
    as Target is not defined.

    What formula do you have in C12?

    C12 is basically where the named range is at. I forgot to edit it in this post. My purpose here is that I only want to trigger the macro when the cell value changes via formula

    I tried to change to Sub Worksheet_Calculate(), but "didn't work". What I mean't by it was that it always loop and loop, until I clicked on the 'stop' icon on the VBA.
    Last edited by dluhut; 09-05-2017 at 08:37 PM.
    Reply With QuoteRegister To Reply
  4. 09-05-2017, 09:07 PM #4
    Maudibe
    Maudibe is offline
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Re: Macro to run when cell value changed via FORMULA (not human intervention)

    Try wrapping your code between Application.EnableEvents statements

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 09-06-2017, 03:07 AM #5
    xlnitwit
    xlnitwit is offline
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Macro to run when cell value changed via FORMULA (not human intervention)

    You will also make maintenance a little easier if you replace
    Please Login or Register  to view this content.
    with this
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.
    Reply With QuoteRegister To Reply
  6. 09-06-2017, 05:40 PM #6
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Macro to run when cell value changed via FORMULA (not human intervention)

    Still doesn't work. Still gives me the same error message
    Reply With QuoteRegister To Reply
  7. 09-06-2017, 07:16 PM #7
    Maudibe
    Maudibe is offline
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Re: Macro to run when cell value changed via FORMULA (not human intervention)

    dluhut,

    The code line:

    = ThisWorkbook.Names("UserSelectCategory").RefersToRange

    returns a range not a string.

    Change your code to something like
    Please Login or Register  to view this content.
    HTH,
    Maud
    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. How Do You Code A Macro With A Pause For Manual Intervention?
    By therealpadd in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-28-2017, 11:12 PM
  2. Macro that AUTOMATICALLY activates another macro without human interference?
    By Mangorni in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-06-2017, 01:18 PM
  3. [SOLVED] Macro that AUTOMATICALLY activates another macro without human interference? HELP
    By Mangorni in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-24-2016, 09:23 AM
  4. Macro And Graphs to stop Manual Data Label Intervention
    By cartica in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-25-2016, 03:22 PM
  5. Run Macro upon cell changed by formula
    By dennisk0 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-21-2007, 04:38 PM
  6. Answer Excel 'yes/no' question in Macro without user intervention?
    By dingman4 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2007, 01:35 PM
  7. [SOLVED] How can I stop a macro for Human Interaction
    By Henry Stock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2005, 07:06 PM

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