Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003

    Activating a VBA routine with a change to a cell value.

    I would like to run a VBA routine when the value in a particular cell on a worksheet changes value. The value appears in the cell as the result of a querie from an outside data source.

    Can anyone help me???

    Last edited by mlitster; 12-23-03 at 17:51.

  2. #2
    Join Date
    Dec 2003
    Hi Marty,

    This example code will watch A1 for a change in value:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
    Case "$A$1"
    MsgBox "Value changed"
    End Select
    End Sub

    But you mentioned (in the email) that the change event didn't work for you.

    You also have the calculate event for the sheet. I'm thinking in the calculate event you could store the target cell value someplace in the workbook, and on calculate, compare the current cell value to the stored previous value. If they differ you could then execute whatever code is needed.

    Depending on what the query is doing, there may be a lot of other options. I would need some help to see what you are doing with that cell and what you are checking for or comparing (and why the change event didn't work). For example, if it is a linked set of data (like from an external database), the link field is a single value - which may be why you see the data in the cell but it doesn't fire the sheet's change event.

    Could you post a sampling of the worksheet and/or provide some more info on the query so I can duplicate it on my side?



Posting Permissions

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