Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Change Event Code To Run Macro When A Cell Value Changes

  1. #1
    Join Date
    26th January 2003
    Location
    Northern Ireland
    Posts
    70

    Change Event Code To Run Macro When A Cell Value Changes

    Hi !

    I use Excel a lot but don't understand VBA much.

    I have looked at a series of Change Event topics and code but can't see what I need.

    I simply want a macro to run automatically when a cell ....which contains the Maximum time from a range.... changes.

    I assume I use .... Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...but I have no idea what code to use...

    can anyone help ??

    Thanks.

    Colin

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th February 2007
    Location
    Melbourne - Australia
    Posts
    156

    Re: Change Event Code To Run Macro When A Cell Value Changes

    This is an example of a worksheet change macro

    It adds 1 to the value in c3 every time a1 or b1 is changed

    Replace cell refences in red with a single cell or a cell range to suit
    Replace Code in Pink with what action you want the macro to take

    VB:
    Private Sub Worksheet_Change(ByVal Target As Range) 
        If Not Application.Intersect(Target, Range("[COLOR="Red"]a1:b1[/COLOR]")) Is Nothing Then 
            Application.EnableEvents = False 
            [COLOR="Magenta"]Range("c3").Value = Range("c3").Value + 1[/COLOR] 
        End If 
        Application.EnableEvents = True 
    End Sub 
    
    
    Last edited by mudraker; March 21st, 2007 at 20:17.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    Northern Ireland
    Posts
    70

    Re: Change Event Code To Run Macro When A Cell Value Changes

    Thanks.....

    The code works only when a value is keyed into the range.

    It doesn't work where there is a formula in the range ... as I have .. =max(b1:b10).

    I have a query on one sheet which will refresh every 15 minutes .... the =max() formula will pickup the most recent Time from the Query and it is this that I need to trigger the firing of the Macro.

    Unless there is another way of doing it ?

    Colin

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,491

    Re: Change Event Code To Run Macro When A Cell Value Changes

    In a bit of a rush, but you could try something like this....

    VB:
    Private Sub Worksheet_Change(ByVal Target As Range) 
        Static old_value As Variant 
         
        If ActiveSheet.Range("C7").Value <> old_value Then 
             'a change has occured in cell C7 so do your processing....
            MsgBox "Changing...." 
            old_value = ActiveSheet.Range("C7") 
        End If 
         
    End Sub 
    
    
    HTH

    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  5. #5
    Join Date
    26th January 2003
    Location
    Northern Ireland
    Posts
    70

    Re: Change Event Code To Run Macro When A Cell Value Changes

    Turns out the "Change event" does not work with a formula .....

    needs "Change Calculate" !

    Colin

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: April 17th, 2008, 11:27
  2. Replies: 3
    Last Post: March 27th, 2008, 23:37
  3. Replies: 6
    Last Post: December 16th, 2007, 10:57
  4. Change Event Code Not Firing
    By MDuvall in forum EXCEL HELP
    Replies: 7
    Last Post: September 27th, 2007, 14:01
  5. Validation Code for Change Event
    By jaymat13 in forum EXCEL HELP
    Replies: 10
    Last Post: July 20th, 2006, 14:40

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
  •  
porno