none
How to run a function when the value of a cell changes in Excel? RRS feed

  • Question

  • Hello,

    I can't figure out how to have a function run when the value in a cell is changed. I have tried the following

     Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            If Target.Address = Range("A6").Address Then
                Beep()
            End If
     End Sub

    But that doesn't seem to work. Any ideas? Please note I am using Visual Studio 2013 and  Office 2013.  Thanks

    Tuesday, January 28, 2014 8:40 PM

Answers

  • Hello John,

    First of all, you need to declare a Worksheet object in the code:

    Dim WithEvents wsheet As Excel.Worksheet

    Then you need to correct the code of your sub:

     Private Sub Worksheet_Change(ByVal Target As Excel.Range) Handles wsheet.Change
            If Target.Address = Application.Range("A6").Address Then
                Beep()
            End If
        End Sub
    Please read more about this in the How to handle events for Excel by using Visual Basic .NET article.
    • Marked as answer by John Martel Tuesday, January 28, 2014 9:21 PM
    Tuesday, January 28, 2014 8:50 PM

All replies

  • Hello John,

    First of all, you need to declare a Worksheet object in the code:

    Dim WithEvents wsheet As Excel.Worksheet

    Then you need to correct the code of your sub:

     Private Sub Worksheet_Change(ByVal Target As Excel.Range) Handles wsheet.Change
            If Target.Address = Application.Range("A6").Address Then
                Beep()
            End If
        End Sub
    Please read more about this in the How to handle events for Excel by using Visual Basic .NET article.
    • Marked as answer by John Martel Tuesday, January 28, 2014 9:21 PM
    Tuesday, January 28, 2014 8:50 PM
  • Great, thanks for your answer. Clearly I am still learning this stuff :grin:
    Tuesday, January 28, 2014 9:22 PM