+ Reply to Thread
Results 1 to 10 of 10

Prevent Use of Escape Key Whilst Editing Cell

  1. #1
    Registered User
    Join Date
    05-26-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    18

    Question Prevent Use of Escape Key Whilst Editing Cell

    Wondered if anyone could help me on this one, after a few failed attempts and browising other similar topics found...

    I have a combination of change event macro before double click event macros on a worksheet.
    I any cell in either Column A or B is double clicked, the cell contents are deleted.
    If another cell is then selected or enter is pressed without any new data being inputted, the previous data is re-populated into the cell (i.e. before the deletion).

    However, if the user double clicks on the cell to edit the data, but then presses the ESC key rather than selecting another cell or pressing enter, the change event macro to repopulate the cell does not run.

    Is there any way of preventing the user from being able to "escape" out of editing a cell, and limiting it to a specific range?
    Failing that, is there any way to run a macro if a user presses escape, whilst editing a cell? (I've tried using OnKey macros, but I can't get them to work whilst the cell contents are being edited)

    Thanks

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Prevent Use of Escape Key Whilst Editing Cell

    What's the purpose of the two events?

    Can you post your current code?
    If posting code please use code tags, see here.

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Prevent Use of Escape Key Whilst Editing Cell

    .
    Untested : https://stackoverflow.com/questions/...n-with-esc-key

  4. #4
    Registered User
    Join Date
    05-26-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    18

    Re: Prevent Use of Escape Key Whilst Editing Cell

    Hi Norie,

    The purpose is to create a use friendly interface and cover as many bases of how a user might enter data, or in this case, how they might begin to enter data, but then escape out of it.

    The change event macro looks at the new data that has been entered into a cell, and using a helper column, adds it to what has been added previously (is a log). It also date stamps each entry.
    If the user exits the cell via ESC, I want it to return the previous data (via the helper column), hence run a macro to pull it back in. This works perfectly if they enter new data, or if they leave it blank and press enter, or click on another cell. It just doesn't work when they press ESC.

    Unfortunately I don't have the current code as it is on a PC elsewhere, but its fairly simplistic.
    One change event looking to see if the cell data has been changed. If so, run macro to add it to the previous data that was in the cell creating a log.
    The other event is the before double click, which will wipe the cell contents when the user clicks on it, so they only need to worry about putting the new entry in. That way formatting will stay consistent with the log.

    If I can prevent the ESC button from working whilst someone is editing the content of a cell, that would solve my issue.

    Hope that helps explain a bit clearer.
    Last edited by wahbob; 11-23-2017 at 04:07 PM. Reason: typo

  5. #5
    Registered User
    Join Date
    05-26-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    18

    Re: Prevent Use of Escape Key Whilst Editing Cell

    Quote Originally Posted by Logit View Post
    Hi Logit,

    I've tried that approach, but it doesn't seem to prevent use of ESC button to exit editing a cell value - although it does prevent macros from being broken via the ESC key.

    Thanks for your help though.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Prevent Use of Escape Key Whilst Editing Cell

    When a cell is in edit mode, which is sounds like what is happening here, code can't run so you can't monitor for the ESC key.

    Why do you have both events?

    Are you cancelling the double click event if/when appropriate?

    Is the change event only ever meant to be triggered by something happening in the double click event?

  7. #7
    Registered User
    Join Date
    05-26-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    18

    Re: Prevent Use of Escape Key Whilst Editing Cell

    Quote Originally Posted by Norie View Post
    When a cell is in edit mode, which is sounds like what is happening here, code can't run so you can't monitor for the ESC key.

    Why do you have both events?

    Are you cancelling the double click event if/when appropriate?

    Is the change event only ever meant to be triggered by something happening in the double click event?

    The two events are mutually exclusive, one to delete the cell contents when someone double clicks on it, and then a change event to create the log after the cell has been changed (re-writing over it).
    If the before double click event wasn't there, then pressing ESC wouldn't be an issue as the cell would revert back to its previous value, but it can't because it gets deleted on double clicking, pressing ESC whilst in edit mode leaves the cell blank.

    Are you saying this isn't possible to prevent the user from pressing ESC whilst in edit mode?
    Last edited by wahbob; 11-23-2017 at 04:15 PM. Reason: typo

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

    Re: Prevent Use of Escape Key Whilst Editing Cell

    Are you saying this isn't possible to prevent the user from pressing ESC whilst in edit mode?
    AFAIK - There is no direct method of doing this. You could probably use external process/program to monitor Excel state. But not through VBA.

    Maybe, launch Application.Inputbox on before double click and have user enter data through that? Instead of editing cell directly.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  9. #9
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Prevent Use of Escape Key Whilst Editing Cell

    .
    I'm not certain I fully understand what your macro code does ... or rather how it does it ... without seeing a sample.
    What I understand is a desire to prevent the ESC key from functioning.

    However, see if this will work for you :

    Please Login or Register  to view this content.

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

    Re: Prevent Use of Escape Key Whilst Editing Cell

    @Logit

    I had the same idea. However, it won't work during cell edit mode.
    FYI - You'd need another sub named "No_Change" that does nothing. Since OnKey is used to fire "No_Change" sub.
    Please Login or Register  to view this content.
    Otherwise, it would throw error.

+ 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. Replies: 2
    Last Post: 09-08-2015, 08:52 AM
  2. [SOLVED] Lock individual cells to prevent editing.
    By TomRet in forum Excel General
    Replies: 3
    Last Post: 01-06-2015, 10:50 PM
  3. Prevent editing more than one cell at a time - get error 1004
    By tv69 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-24-2014, 10:00 AM
  4. Prevent direct editing, but allow userform to update cell
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-07-2011, 05:09 PM
  5. Prevent editing:opens automatically
    By pmguerra in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-23-2010, 07:58 AM
  6. Prevent editing
    By pmguerra in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-23-2010, 07:28 AM
  7. Highlighting cells whilst editing
    By Loonia in forum Excel General
    Replies: 1
    Last Post: 02-16-2006, 10:00 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