Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I want to take an action when a particular cell changes in Excel 2003, so I've written a simple bit of VBS.

    It isn't working, so to test it I've tried the following:

    Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox Target.Address
    End Sub

    This works fine: whenever I change a cell, the address of the changed cell is displayed as I would expect. In my case it is correctly displaying $c$6.

    But if I write the following, nothing happens:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$c$6" Then
    MsgBox Target.Address
    End If
    End Sub

    What am I doing wrong ? Something amiss with the If statement maybe ?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    The usual technique for checking which cell has changed is to use syntax like...

    Code:
    If Intersect(Target, Range("$C$6")) Is Nothing Then
     ' Not our cell
    Else
     ' Our cell has changed
    End If

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Quote Originally Posted by StuartR View Post
    The usual technique for checking which cell has changed is to use syntax like...

    Code:
    If Intersect(Target, Range("$C$6")) Is Nothing Then
     ' Not our cell
    Else
     ' Our cell has changed
    End If
    Thanks Stuart, that's got me on the right track.

    Just curious why my earlier syntax didnt' work though ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By default, text comparisons are case sensitive in Excel VBA. It would have worked if you had used

    If Target.Address = "$C$6" Then


  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Hans - I've been writing Excel code for years without ever noticing that !!!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you'd like to make text comparisons not case sensitive, insert a line

    Option Compare Text

    at the top of the code module, before all procedures and functions (just like Option Explicit)

    See the Excel VBA help or Option Compare Statement.


  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Useful, as ever, thanks

Posting Permissions

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