Originally Posted by
geekgirlau
Hi SpeedRacer,
Actually I didn't do a great deal.
New procedure:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
KeyCellsChanged
End If
End Sub[/vba]
This code automatically runs when you change cells on Sheet1. The procedure itself is very similar to what you already had, where it checks to see which cell has changed. The "Target" parameter gives you the range that was changed automatically, so you just need to check that the range is within A1:A3.
Deleted Procedures:
"Auto_Open" and "DidCellsChange" - these are no longer required, as the Sheet Change event handles this. To delete a procedure or function, you can just select the text and press Delete - it's just treated as text in the VBE window. If you wanted to delete an entire module, right-click the module in the Project Explorer on the left and select "Remove".
Other Changes:
The only other change is within the "KeyCellsChanged" procedure. At the top I've added
[vba]Application.EnableEvents = False[/vba]
and at the bottom of the code
[vba]Application.EnableEvents = True[/vba]
Because your code changes cells on Sheet1, this would normally trigger the change event again. By setting EnableEvents to false, any changes your code makes don't trigger the Sheet1 macro until your code has finished.