Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto run code (XP)

    How can I get a bit of code to run when the value of a cell changes to a certain value? I have some code that works but I can't trigger it automatically.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto run code (XP)

    You need a worksheet-level event procedure for this.
    - In the Visual Basic Editor, make sure that the Project Explorer is visible (Ctrl+R).
    - If necessary, expand your workbook and "Microsoft Excel Objects" under it.
    - Double click the node corresponding to the worksheet that contains the cell.
    - This will open the code module of the worksheet.
    - From the Object dropdown list in the upper left of the module window, select Worksheet.
    - From the Procedure dropdown list in the upper right, select Change.
    This will create the outline of the On Change event handler:

    Private Sub Worksheet_Change(ByVal Target As Range)

    End Sub

    This event will fire each time you modify a cell. Target indicates the range of cells that is being changed. You can user Intersect to check if a particular cell is being changed, for example, if you want to test if C3 has value 37:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C3")) Is Nothing Then
    If Range("C3") = 37 Then
    Application.EnableEvents = False
    ' insert or call code to be executed here
    Application.EnableEvents = True
    End If
    End If
    End Sub

    Note: if your code is going to modify cells, in particular C3 itself, it is wise to prevent events from being processed temporarily, otherwise you might get caught in an endless loop. That is the purpose of the Application.EnableEvents lines.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto run code (XP)

    What if the cell I want to use as a trigger is the Page selection option of a pivot table?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto run code (XP)

    That should work too - just use the address of the Page selection cell. If you want to trap a text choice, such as (All), surround it in quotes:

    ...
    If Range("C3") = "(All)" Then
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •