Excel 2003: Run a macro when you change a cell

[Lou Sander]

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.

[RetiredGeek]

Lou,

This should do the trick.

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!

[rory]

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:

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

[RetiredGeek]

Rory,

Thanks, much better code.

[Lou Sander]

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.

[rory]

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.

[Lou Sander]

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.

[rory]

Right-click the sheet tab, choose view code, and paste the code in there. You can refer to range("plaintext") in the code.

[Lou Sander]

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!