Intercepting data typed into cells.
"GS" wrote:
Yes, this is easily done! There are a few approaches you can go with but
the most simple is to store a delimited string of the 'special' cell
addresses in a module-level constant and check if 'Target' address is
InStr(msSpclCells)...
Const msSpclCells$ = "$A$1,$B$1,$C$1,$D$1,$E$1,$F$1,$G$1,$H$1" _
& "AA1,$AB$1,$AC$1,$AD$1,$AE$1,$AF$1,$AG$1"
..and so on where you list the addresses in absolute format as shown. Then
in the _Change event...
If InStr(msSpclCells, Target.Address) 0 then...
..so if the address is NOT in your list the If..Then..End If is skipped
and the next executable line is 'End Sub'!
Okay, this seems like a good solution. But....I was curious if VBA Excel
had something more like a "Cell_Change" event for each individual cell?
The Worksheet_Change function still involves doing an InStr check or
a Select-Case statement if ANY cell on the spreadsheet is changed.
I do like your solution though. It seems like it would be faster than my
solution, which involved a big Select-Case construction to test if the
changed cell was one of the "special" cells.
|