+ Reply to Thread
Results 1 to 6 of 6

Prevent Typing in Drop Down List Cells

  1. #1
    Registered User
    Join Date
    06-19-2011
    Location
    PA
    MS-Off Ver
    Excel 2010(Win)/2011(Mac)
    Posts
    15

    Prevent Typing in Drop Down List Cells

    I'm trying to prevent users from typing in a cell which has a drop down list through data validation. Can anyone suggest some code to accomplish this?

    The issue is that if you type something not contained in the list Excel 2010 gives an error message and then the user can click "Retry". However Excel doesn't clear the cell and the user is unable to access the drop down list unless the cell is clear.

    My other thought is possibly some code that reacts to the data validation message and clears the cell when they select "Retry", but I couldn't find anything regarding that type of event.

    Many thanks!

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Prevent Typing in Drop Down List Cells

    If you teach your operators to click on CANCEL instead of RETRY, then the old value will replace what they tried to put in.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Registered User
    Join Date
    06-19-2011
    Location
    PA
    MS-Off Ver
    Excel 2010(Win)/2011(Mac)
    Posts
    15

    Re: Prevent Typing in Drop Down List Cells

    Thanks for the suggestion but I'm trying to avoid additional instructions for the user as much as possible

  4. #4
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: Prevent Typing in Drop Down List Cells

    Just turn off the error messages. No message means no retry to click. it's the error alert tab when you create/edit the data validation.

  5. #5
    Registered User
    Join Date
    06-19-2011
    Location
    PA
    MS-Off Ver
    Excel 2010(Win)/2011(Mac)
    Posts
    15

    Re: Prevent Typing in Drop Down List Cells

    I don't think that will fly either, I'm really looking for a more elegant solution involving VBA rather than a workaround

  6. #6
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: Prevent Typing in Drop Down List Cells

    Turn off the error messages, script in on the worksheet_change to detect if the value is in the list or not, then use script to clear the box and/or give them a message box telling to pick from the list. See the attached.
    list message.xlsm

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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