Intercepting data typed into cells.
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? |
Intercepting data typed into cells.
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 |
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. |
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. 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 |
Intercepting data typed into cells.
Do you think data validation will help ? Worksheet_change with range set for key cells will also work.
|
All times are GMT +1. The time now is 04:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com