Trigger change event BEFORE cell edit

I'm trying to make a form for my work and part of it is a roster of who is involved on project. I'm trying to make the form really slick but I ran into a little roadblock and I'm not sure if there's a workaround.

Here's what I want:

#	Name	Position
1	John Smith	Sales
	Add New Team Member...

I want the user to be able to edit the "Add New Team Member" cell and as soon as they do, the cell clears so that it's blank when they start typing a name. Later, after the user enters a name, I want to make the next row say "Add New Team Member", but that's an easy task with a regular old change event. It's the clearing action prior to the user adding a name that I need help with.

The closest I've gotten is using a BeforeDoubleClick change event, but this limits the code to run only on a Double-click event. I also need Excel to capture the F2 edit method as well as the typical "SelectCell/JustType" method of cell editing too. Unfortunately it doesn't look like there's a BeforeCellEdit change event so I need a workaround.

If I can't find a workaround, I may just have to settle for a Selection Change event but I'd be a little disappointed.

Any ideas?

The text in the cell should automatically disappear when a user starts to type in the cell. That is an Excel default. Have you tried it?

Does this help

Please Login or Register  to view this content.

You can bind F2 to a macro.

Please Login or Register  to view this content.

Replace SubName with name of your Sub.

Then make sure to unbind the key when user closes workbook or switches to another sheet/workbook.

Please Login or Register  to view this content.

Unbind code should go in few locations: "Worksheet_Deactivate()", "Workbook_Deactivate()" and "Workbook_BeforeClose()"

Interesting. This is something I wanted to apply to forms I'm creating on other worksheets (within the same workbook). Is there any way I can pass a variable through from the change event into the sub?

For instance I just tried:

Please Login or Register  to view this content.

in the Worksheet_Activate() Sub and then made a module:

Please Login or Register  to view this content.

but I got an "Argument not optional" error. I'm hoping that's because I have the wrong syntax and not because I actually can't do this. I'd like to be able to use the same EditCheck code on multiple worksheets rather than a bunch of little modules for each sheet. I just need to somehow pass the worksheet name to the sub so the sub knows which cell to modify.

Originally Posted by Fattyfatfat Kid

Richard Buttrey - Your code would help except it would only trigger after the user edits the cell. I don't want the user to edit the cell and have to backspace the "Add New Team Member..." message every time.

If you want the Active cell after the change to be the 'Add New Team Member' then just include an extra line of code. i.e.

Please Login or Register  to view this content.

When you press F2, to enter edit mode, the cell holding “add new...” must already be selected.
Just use Selection in your Editcheck code, rather than passing it as argument.

Originally Posted by Fattyfatfat Kid

Ohhh I can use Selection.Worksheet.Name to pull the Worksheet name, nice!

Okay, I think I have enough here to make this thing happen now, thanks guys!

We seem to have drifted a long way from your original but a simpler way of getting and using any sheet name that you select is to use the following Workbook Sheet Activate event

Please Login or Register  to view this content.