Results 1 to 9 of 9
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts

    Excel 2003: Run a macro when you change a cell

    When I change cell A1 in my workbook, I want to automatically run a macro that clears some other cells. I've got that cell-clearing macro, but I don't know how to make it run when somebody changes what's in A1.

    This is (kind of) covered in a book I have, but I can't make sense of what they are saying.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,406
    Thanks
    208
    Thanked 833 Times in 766 Posts
    Lou,

    This should do the trick.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Address <> "$A$1" Then Exit Sub
       MsgBox "You just changed Cell A1", vbOKOnly + vbInformation, "Status Message"
    End Sub


    P.S. Make sure you place this code in the Worksheet you want it to operate on!
    Last edited by RetiredGeek; 2011-08-11 at 20:54. Reason: Add'l Info
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 Posts
    IMO, Intersect is better than an address check and you should disable events if you are going to then change other cells on the sheet:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Not Intersect(Target, Range("A1")) Is Nothing Then
          On Error Resume Next
          Application.EnableEvents = False
          Call SomeMacroHere
          Application.EnableEvents = True
       End If
    End Sub
    Regards,
    Rory
    Microsoft MVP - Excel.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,406
    Thanks
    208
    Thanked 833 Times in 766 Posts
    Rory,

    Thanks, much better code.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2011-08-12)

  6. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    Thanks fellows, but in my hands it isn't working.

    I use rory's code to call ClearALine, which works when I call it from a Command Button.

    When I change cell A1 (actually B3 in my case), nothing seems to happen.

    I didn't mention it, but this workbook has two worksheets, Encryptor and Solver. I want it to work on the Encryptor sheet.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 Posts
    The code needs to be in the module for the encryptor sheet, and it assumes the value of the cell is being changed by hand, not as the result of a formula calculation.
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. #7
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    The cell is definitely being changed by hand.

    I'm kind of clueless about how the Modules fit in with things.

    The workbook formerly had several modules, created by recording macros. To organize things (in my own mind, as opposed to any knowledgeable plan), I moved some things from one module to another.

    Currently there are:

    Module1, which formerly contained some macros from a deleted worksheet named OLDSolver

    Module2, which has 3 Subs and 2 Functions, all having to do with the Encryptor sheet. Your Sub is here at the top.

    Module3, which has 2 Subs having to do with the Solver sheet.

    I don't know if or how these sheets are associated with any worksheet, other than my arbitrarily putting these Subs and Functions into them.

    BTW, I'd love to be able to refer to the changing cell ($B$3) by its name (PlainText) rather than its location. I don't quite know how to do that.
    Last edited by Lou Sander; 2011-08-12 at 12:52.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 Posts
    Right-click the sheet tab, choose view code, and paste the code in there. You can refer to range("plaintext") in the code.
    Regards,
    Rory
    Microsoft MVP - Excel.

  10. The Following User Says Thank You to rory For This Useful Post:

    Lou Sander (2011-08-12)

  11. #9
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    It's working perfectly!

    Thanks again for all the help. Without the Lounge, I'd be struggling with elementary things, or, more likely, just giving up on making this thing work the way I want it to.

    Every tip I get from the Lounge leads to some new understanding, or a recollection of something I had forgotten. It's wonderful!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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