I have about 50 scattered cells on my spreadsheet that are
"special". If a user types data into any of these cells, I would
like to immediately check what they typed, modify it how I want,
and then enter the new data into the cell.
For example, suppose a user types "eggs" into cell A1.
I want to change this to "green eggs" immediately after
they type "eggs" in cell A1 and press the Enter key.
I know that this will require me to use the "Worksheet_Change()"
event or subroutine. Is this the only way to handle changes to
the spreadsheet?
I guess I'm just worried about performance. To me it
doesn't make sense to check when ANY cell is changed. It
would be great if I could limit Worksheet_Change() to
only be activated when one of my 50 cells is changed.
Is this possible?
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'!
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion