Execute a macro when the contents of A1 changes

  • Thread starter Thread starter paul.nielson
  • Start date Start date
P

paul.nielson

I’m trying to get a marco embedded in a worksheet to run when A1
changes, I’m using

Private Sub Worksheet_Change(ByVal Target As Range)

To run the code with

If ActiveCell = Cells(2, 1) Or ActiveCell = Cells(1, 2) Then

To test if the current cell is an adjacent cell to A1 has been
selected. This seems to be an inefficient method and not 100% reliable
has anyone a better suggestion



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------
 
What you have written compares the VALUE in the active cell with the VALUE in the cell below and
the VALUE in the cell to the right.

You want to know whether two object variables point to the same object. For that, the syntax is

If ActiveCell Is Cells(2, 1) Or ActiveCell Is Cells(1, 2) Then

Note "Is", not "=".

All of that said, "Is" doesn't work. It should, but it doesn't.

Target is the cell that was just changed. If changing A1 is the important event, your code
should refer to Target, not to ActiveCell. (Remember, the user may have the worksheet set to
move the cell pointer in any of the 4 directions or not move it at all, when he/she presses
Enter, so you can't count on the active cell being A2 or B1. It could still be A1.)

Assuming that you want the code to run when the user puts new data into A1, then write it as

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then

OTOH, you talk about "has been selected". If you are concerned with the user selecting a new
cell, rather than changing the contents of A1, then you should use the Worksheet_SelectionChange
event, not Worksheet_Change. The former is triggered by just selecting a new cell. The latter is
triggered only when the user enters/edits a value in a cell.
 
Myrna:
Thanks for your comments. You are truly teaching here; better than most
excel books I own. When I see your name (as a responder) I automatically
"jump in" as I know I will learn something new and helpful (that I
previoulsy didn't understand).
Thanks,
JMay

Myrna Larson said:
What you have written compares the VALUE in the active cell with the VALUE in the cell below and
the VALUE in the cell to the right.

You want to know whether two object variables point to the same object. For that, the syntax is

If ActiveCell Is Cells(2, 1) Or ActiveCell Is Cells(1, 2) Then

Note "Is", not "=".

All of that said, "Is" doesn't work. It should, but it doesn't.

Target is the cell that was just changed. If changing A1 is the important event, your code
should refer to Target, not to ActiveCell. (Remember, the user may have the worksheet set to
move the cell pointer in any of the 4 directions or not move it at all, when he/she presses
Enter, so you can't count on the active cell being A2 or B1. It could still be A1.)

Assuming that you want the code to run when the user puts new data into A1, then write it as

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then

OTOH, you talk about "has been selected". If you are concerned with the user selecting a new
cell, rather than changing the contents of A1, then you should use the Worksheet_SelectionChange
event, not Worksheet_Change. The former is triggered by just selecting a new cell. The latter is
triggered only when the user enters/edits a value in a cell.
Click to expand...
 
Thank you for that comprehensive response to my enquiry. I copied your
line of code
If Target.Address = "$A$1" Then
And the whole macro worked a treat. I couldn’t find anything in the
documentation I have to explain what you put together and I now
understand how the Private Sub Worksheet_Change(ByVal Target As Range)
statement works.

Just to explain a little more about the project. It’s an attendance
register with 31 date columns with personnel names in the first column,
these are printed off several months in advance. The date is entered
in cell A1 (Hence the test for A1 value change) and the macro hides the
end columns for the months that are less than 31. Conditional
formatting within the worksheet colours the weekends and a lookup
statement finds public holidays for a second condition. Once again
thanks for the help.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------
 
Back
Top