XL7: "Pick From List" Not Available in Data Entry Mode

Last reviewed: March 27, 1997
Article ID: Q137920
7.00 WINDOWS

The information in this article applies to:

  • Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, when you are in Data Entry mode, you may not be able to use the "Pick from list" feature.

CAUSE

When Microsoft Excel is in Data Entry mode, you cannot use scroll bars, you can only select certain cells, and many menu commands are unavailable (dimmed). One of the commands that is unavailable is the Pick From List command (to see this command, click the right mouse button in any cell).

WORKAROUND

Method 1: Instead of using the shortcut menu to get the Pick List, you can

          press the Alt and Down Arrow keys. This will bring up a list of
          all the unique items in the active column, and you can choose the
          item you want from this list.

Method 2: Turn off Data Entry Mode. There are two ways to turn off Data
          Entry Mode in Microsoft  Excel, and the method you use will
          depend on the setting of the  DataEntryMode property. If the
          DataEntryMode property is set to  xlOn, then you can exit Data
          Entry Mode by pressing the Escape  key. If the DateEntryMode
          property is set to xlStrict, then you  can only exit Data Entry
          Mode by running another macro that sets  the property to xlOff.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The Pick List is part of the new AutoComplete feature of Microsoft Excel version 7.0. The AutoComplete feature scans the active column for unique entries, and when you bring up the Pick List, all of the unique entries are displayed in a list box. Simply clicking on an item in the Pick List will enter that item in the active cell, saving you from having to retype something that already has been entered.

The DataEntryMode property is usually set to restrict the user to entering data into a specific range of cells on the worksheet. As an example to see how Data Entry Mode works, do the following:

  1. Open a new workbook.

  2. Select cells A1:B5 on Sheet1.

  3. On the Format menu, click Cells, and in the Format Cells dialog box, click on the Protection tab.

  4. In the Protection tab, click the Locked check box to remove the check, and then click the OK button.

  5. In a module sheet, enter the following macro code:

Microsoft provides examples of Visual Basic for applications procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

Sub Data_Entry_Test()

      'The DataEntryMode property can be set to three values:

      'xlOn will restrict you to working in the cells that
      'are selected before the macro is run. You can press
      'the escape key to turn off DataEntryMode.

      'xlOff will turn DataEntryMode off.

      'xlStrict will also restrict you to working in the cells
      'that are selected before the macro is run, but you cannot
      'turn off DataEntryMode by pressing the escape key. If the
      'DataEntryMode property is set to xlStrict, you need to run
      'another macro that sets it to xlOff in order to turn of
      'Data Entry Mode.

      Application.DataEntryMode = xlOn

    End Sub

  • Click on the tab for Sheet1 to activate it.

  • Select cells A1:B5 on Sheet1, if they are not already selected.

  • Run the macro from step 5.

    You should now be able to only work with the cells in A1:B5.

  • Press the Escape key to turn off Data Entry Mode.


  • KBCategory: kbprb
    KBSubcategory:

    Additional reference words: 7.00 picklist gray grey greyed grayed out
    Keywords : kbprb
    Version : 7.00
    Platform : WINDOWS


    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: March 27, 1997
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.