+ Reply to Thread
Results 1 to 4 of 4

How to only execute onderlying macro's when cell value changes

Hybrid View

  1. #1
    Registered User
    Join Date
    01-06-2019
    Location
    Amsterdam
    MS-Off Ver
    Office 365
    Posts
    12

    How to only execute onderlying macro's when cell value changes

    Hello, I hope someone can help me because I seem to be screwing up more than I should be.

    I have made an excel sheet which shows or hides a graphs based on the value of a cell
    if: "deze maand" then show graph 1 and hide graph 2.
    if: "Dit jaar" then show graph 1 and 2.

    In order to make this work automatically, I have also made a macro that changes a cell fill colour when either cell values are correct.
    This because excel seems to need an action in order to update the changes.

    The problem is that now that on any other cell changes, the Both macro runs. Deleting its history and making it impossible to use the "Undo" button.

    Is there any way I can adjust this code, so it first checks if there are any changes in cell a3, before executing the underlying steps.
    This so that the undo button is still in function and the cell fill action is only executed when the correct cell value changes (through a drop-down menu)

    _____________________________________________________________________________________________________________________________
    #
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Range("T6").Value = "Deze maand" Then
              Sheets("Overview").ChartObjects("Grafiek1").Visible = True
                Sheets("Overview").ChartObjects("Grafiek2").Visible = False
    
    ElseIf Range("T6").Value = "Dit jaar" Then
                
             Sheets("Overview").ChartObjects("Grafiek1").Visible = True
                Sheets("Overview").ChartObjects("Grafiek2").Visible = True
                            
         End If
    End Sub
    
    Sub worksheet_change(ByVal Target As Range)
    Set Target = Range("T6")
    If Target.Value = "Deze maand" Then
     Call DezeMaand
    End If
    If Target.Value = "Dit jaar" Then
    Call DitJaar
    End If
    End Sub
    _____________________________________________________________________________________________________________

    I really hope someone could help with this, it's the last step of the process before I can send it out :-(

    Thanks in advanced!
    Last edited by MrDutch; 01-12-2019 at 02:33 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to only execute onderlying macro's when cell value changes

    Maybe the change event only:

    Sub worksheet_change(ByVal Target As Range)
    If Target.Address="$T$6" Then
    If Target.Value = "Deze maand" Then
    Sheets("Overview").ChartObjects("Grafiek1").Visible = True
    Sheets("Overview").ChartObjects("Grafiek2").Visible = False
    
    ElseIf Target.Value = "Dit jaar" Then
    Sheets("Overview").ChartObjects("Grafiek1").Visible = True
    Sheets("Overview").ChartObjects("Grafiek2").Visible = True
    End If:End If
    End Sub
    BTW:

    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    01-06-2019
    Location
    Amsterdam
    MS-Off Ver
    Office 365
    Posts
    12

    Re: How to only execute onderlying macro's when cell value changes

    My god, it works perfectly!
    I've fighting this for hours, I can't thank you enough!

    I've entered your code and deleted the second part of the macro, now it works like clockwork :-)

    Thanks again!


    ps: apologies for wrongly posting my original code, I have edited the post!

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to only execute onderlying macro's when cell value changes

    You're welcome amd thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Macro to execute a vlookup from A1 to last cell used
    By pinky2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2017, 06:52 PM
  2. [SOLVED] Execute macro based on cell value
    By Maike in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-25-2017, 08:38 AM
  3. [SOLVED] How to execute a macro when a cell value changes?
    By sylvainsyl20 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-20-2016, 09:22 AM
  4. [SOLVED] Execute macro on cell value change
    By tom.mat.walker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2015, 05:14 AM
  5. Execute a macro in a closed workbook, or open and execute without the user noticing
    By cearaujo123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2015, 06:44 PM
  6. Execute Cell with a Macro (timer)
    By flds in forum Excel General
    Replies: 6
    Last Post: 05-13-2010, 07:26 AM
  7. macro to execute code from cell.value?
    By wamp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2010, 04:29 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