XL97: Cannot Move Among Unlocked Cells in Scroll Area with TAB

Last reviewed: March 13, 1998
Article ID: Q161625
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

When you use a Visual Basic for Applications macro to cycle through unlocked cells, you cannot use the TAB or arrow key to move the active cell through the unlocked cells. However, you can select an unlocked cell in the scroll area with the pointer.

CAUSE

This problem occurs when all of the following conditions are true:

  • The macro restricts the scroll area to a specific range of cells by using the ScrollArea property.

    -and-

  • The worksheet contains multiple (noncontiguous) selections of unlocked cells.

    -and-

  • You unlock cells outside of the scroll area and use the TAB or arrow key to move between unlocked cells.

Pressing the TAB or arrow key fails because these keystrokes function by determining the next unlocked cell that is to the right or below the active cell, and these cells are outside the restricted scroll area.

WORKAROUND

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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

To work around this problem, temporarily lock all cells that are outside of the scroll area. To do this, create a Visual Basic macro that limits the cells that can be selected on the worksheet. This macro must unlock cells that can be selected, protect the worksheet, and then restrict the scroll area to encompass the cells that can be viewed.

NOTE: By restricting the scroll area, you cannot select some cells using the TAB or the arrow key.

Visual Basic Macro Example

The following macro example locks cells outside of the scroll area on Sheet1 of a new workbook:

   Sub Set_Restrictions()
   
       Dim myArea As Object
       Dim counter As Integer
   
       Range("B5:C10").Locked = False
       Range("C14:D16").Locked = False
   
       With Worksheets("sheet1")
           .EnableSelection = xlUnlockedCells
           .Protect contents:=True, userinterfaceonly:=True
           .ScrollArea = "a1:e20"
       End With
   
       Set myArea = Range(Worksheets("sheet1").ScrollArea)
   
       For counter = (myArea.Column + myArea.Columns.Count + 1) To 256
           Columns(counter).Locked = True
       Next counter
   
   End Sub

To reset the scroll area and avoid putting restrictions cells that can be selected, run the following macro:

   Sub No_Restrictions()
       With Worksheets("sheet1")
           .EnableSelection = xlNoRestrictions
           .Protect contents:=False, userinterfaceonly:=True
           .ScrollArea = ""
       End With
   End Sub

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.

REFERENCES

For more information about the Locked property, click the Index tab in Microsoft Visual Basic Help, type the following text

   locked

and then double-click the selected text to go to the "Locked Property" topic.


Additional query words: XL97 8.00
Keywords : kbcode xlvbahowto
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb


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 13, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.