[Robert Crandal[_3_]]

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?

[GS[_2_]]

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

[Robert Crandal[_3_]]

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

[GS[_2_]]

"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

[Abhijeet Gudur]

Do you think data validation will help ? Worksheet_change with range set for key cells will also work.