"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.

Given that the default object of the _Change event is a ref to the
range of cells being edited/changed, it's as close as it gets. It
wouldn't make sense IMO to have a Cell_Change event since it's possible
to edit multiple cells simultaneously. It can be more complex as in the
Farpoint Spread.ocx ActiveX spreadsheet control where indexes to the
col & row are refs to the cell[s] being changed. Same event
(fpSpread_Change), but the ref scheme follows that of a grid control
(or most any other multi-row/col control) where we have to use indexes
to the 'target' cell[s].

FWIW
I devised this methodology when I stopped using controls on worksheets
in favor of using cells 'disguised' as buttons to execute code. This
allowed me to place these anywhere on the sheet with reliable
positioning within ranges without worry about unexpected repositioning
when rows/cols were hidden/unhidden. A Select Case construct would
certainly be appropriate, though, if need be. I've also used an array
of value pairs (Address=ProcedureName) so I could reduce code to a loop
that uses CallByName. This would use a Before..Click event (double or
right), though, instead of the Change event.<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion