How to identify changed cell
Posted by JAF on October 20, 2000 6:12 AM
I have a large spreadsheet (24 columns and up to 5000) rows in which I need to stop the people who will be updating it from entering a space character (or characters).
Some people who will be updating the spreadsheet do not have a particularly high level of Excel knowledge, and have been known in the past to input space characters in order to "delete" cell contents - going on the basis that if they can't see any text in the cell then it must be empty!
What I want to do is to put in a Worksheet_Change command to prevent this, but I need to know how to identify the cell that was last changed. - Any suggestions as to how I can do this.
Unfortunately it's not just a simple matter, as these users enter data in a variety of ways - some use tab (which moves the activecell one cell to the right, some use Return which moves the activecell one cell down and others click in another cell in order to accept their input.
I can't use Data Validation, as this is already used for some other settings.
Help!!!!!
JAF
Posted by JAF on October 20, 2000 6:35 AM
Clarification of Problem
I've just re-read my posting and realised that it's a bit confusing.
To summarise, what I want to be able to identify whatever the last cell was that was changed (and it's previous value if at all possible) WHENEVER the value that is entered is (or starts with) a space character.
JAF
Posted by Tim Francis-Wright on October 20, 2000 7:41 AM
Putting something like the following in the
VBA code for the sheet in question might help.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim c As Object
Dim Offender As String
For Each c In Target.Cells
If Left(c, 1) = " " Then
Offender = c.Address
MsgBox "You cannot start an entry with a space character!", vbExclamation
Application.Undo
End If
Next
End Sub
I assumed that all you want to do is to undo the
user's spacy entry, so I used Application.Undo;
you might wish to take other measures. In any
case, Offender is the cell in question.
(If you want to limit this to a particular
region of the sheet, you can have an If..then
statement that checks if the Intersect of
Target and some other range is not Nothing.)
HTH!
