Announcement

Collapse
No announcement yet.

Change Event Code To Run Macro When A Cell Value Changes

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

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

    Comment


    • #3
      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

      Comment


      • #4
        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....

        Code:
        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

        Check out our new reputation system. Click on the "star" under the post!
        _______________________________________________

        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

        _______________________________________________

        Comment


        • #5


          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

          Comment

          Working...
          X