+ Reply to Thread
Results 1 to 11 of 11

Trigger change event BEFORE cell edit

  1. #1
    Registered User
    Join Date
    09-30-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    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?
    Last edited by Fattyfatfat Kid; 05-10-2019 at 07:49 PM.

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Trigger change event BEFORE cell edit

    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?
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,341

    Re: Trigger change event BEFORE cell edit

    Does this help

    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    09-30-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Trigger change event BEFORE cell edit

    JLGWhiz - Wow, I feel dumb, you're right of course. I'm just so used to using F2 to edit cells, it didn't occur to me that the click/type edit method isn't a problem haha!

    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.

    Okay I'm thinking I can use a BeforeDoubleClick to capture the Double-click method of editing and the click/type edit method will take care of itself (thanks JLGWhiz). Is there any way to capture the F2 method? I don't think many people at my work really use this editing method so I may just skip it if there isn't an easy way.

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Trigger change event BEFORE cell edit

    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()"
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  6. #6
    Registered User
    Join Date
    09-30-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Trigger change event BEFORE cell edit

    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.
    Last edited by Fattyfatfat Kid; 05-10-2019 at 05:41 PM.

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,341

    Re: Trigger change event BEFORE cell edit

    Quote Originally Posted by Fattyfatfat Kid View Post
    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.

  8. #8
    Registered User
    Join Date
    09-30-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Trigger change event BEFORE cell edit

    Quote Originally Posted by Richard Buttrey View Post
    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.
    This code will still fire after the user edits the cell, wont it? I need Excel to clear the cell before the user begins their edit because the cell will already have "Add new Team Member..." in it. But like JLGWhiz said, this automatically happens naturally if I just click on a cell (not double-click) and start typing so my question is already mostly solved. Just trying to capture the F2 method of cell editing now.

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Trigger change event BEFORE cell edit

    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.

  10. #10
    Registered User
    Join Date
    09-30-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Trigger change event BEFORE cell edit

    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!

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,341

    Re: Trigger change event BEFORE cell edit

    Quote Originally Posted by Fattyfatfat Kid View Post
    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.
    Last edited by Richard Buttrey; 05-11-2019 at 04:40 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Event trigger via change in row height
    By kev_ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-23-2017, 11:18 AM
  2. Replies: 8
    Last Post: 08-06-2014, 04:41 AM
  3. [SOLVED] VBA to trigger event change
    By jrholden in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-26-2014, 11:34 AM
  4. [SOLVED] Trigger Change Event
    By alienware in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-27-2012, 07:25 AM
  5. Select Box Change Event Trigger
    By excelsupportforum in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-31-2012, 12:45 PM
  6. Cell value change to trigger macro (worksheet change event?)
    By Neil Goldwasser in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2006, 10:00 AM
  7. [SOLVED] Worksheet Change Event-change event to trigger
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-18-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1