|First, let me state that I have tested my code in Excel 2003 only, but I don't believe that it should act any differently in any other version.|
re: "CELLS property in RANGE OBJECT" ... "...is -RELATIVE- to the UPPER LEFT cell..."
The Cells Property that Mr. Walkenbach is referring to in this section is not related to the code I offered. The Cells property can be used to refer to a cell by number, and that number is relative to the upper left cell of the range being used.
- Cells(1) of B2:C3 is B2, the upper left cell in the range
- Cells(2) is C2, one cell to the right
- Cells(3) is B3, etc.
Simply start in the upper left corner of the range and count to your right until you get to the edge of the range and then go down one row and all the way over to the left.
Interestingly enough, Cells(5) of B2:C3 will return B4 even though it is outside of the specified range. Excel will just keep counting cells, relative to the upper left cell but only as wide as range object that was specified.
Run this to see a real life example:
MsgBox "Cells(1) of B2:C3 is: " & Range("B2:C3").Cells(1).Address
MsgBox "Cells(2) of B2:C3 is: " & Range("B2:C3").Cells(2).Address
MsgBox "Cells(3) of B2:C3 is: " & Range("B2:C3").Cells(3).Address
MsgBox "Cells(5) of B2:C3 is: " & Range("B2:C3").Cells(5).Address
So, again, that has nothing to do with the Selection_Change code that I offered.
That said, I have no idea what is going on with your spreadsheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target = "" Then Target = Now()
This code monitors the user's cell selection and checks for 2 things:
Is the selected cell in Column 1 (A)?
Is the selected cell empty?
If both of these conditions are met, it puts the current value of NOW() in the target (selected) cell only. Not the formula =NOW() and not the words NOW(). If I ran it right now, it would put 08/04/2009 6:10:39 PM in the cell.
In fact, it is so rudimentary, it will error out if you select more than one cell at a time, so there is no way it can write NOW() (or it's value) into any more than 1 cell. However, if you have some other code running that gets triggered by a change in Column A, that's not something I would know about.
May I suggest you start with a brand new workbook, paste my code into the Sheet1 module and then select a single cell in Sheet1!Column A. It should put the current date and time in the cell.
Next, select another cell in Column A, type something over the date and time that appears and then select a cell in any other column. Your data should show in the cell in Column A and nothing should happen in the newly selected cell, or any other cell. Now go back and select the cell you just entered data into. Nothing should happen since the cell isn't empty.
Next select more than 1 cell anywhere in the workbook and the code should throw up a Type Mismatch error. Note: If you click End, the code will be ready to run the next time you select a cell. If you click Debug, it will take you into the VBA editor and the line that caused the error will be highlighted. You'll need to stop the code before you can run it again.
If anything other that what I described above happens when you run the code in a new workbook, I will be very surprised.