XL98: Cannot Move Among Unlocked Cells in Scroll Area with TAB (186206)

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 professionals 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 temporarily work around this problem, 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 key or the arrow keys.

Visual Basic Macro Example

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

1. Save and close any open workbooks, and then create a new workbook.
2. Press OPTION+F11 to start the Visual Basic Editor.
3. On the Insert menu, click Module
4. Type the following code in the module:

      Sub Set_Restrictions()
   
          Dim myArea As Object
          Dim counter As Integer
   
          Range("B6:C10").Locked = False
          Range("C15:E16").Locked = False
          Range("F12:H15").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) To 256
              Columns(counter).Locked = True
          Next counter
   
      End Sub
   						

5. Press OPTION+F11 to switch to Microsoft Excel.
6. On the Tools menu, point to Macro, and then click Macros.
7. Click the Set_Restrictions macro, and then click Run.

The cells are locked outside of the scroll area on Sheet1 of a new workbook.

To reset the scroll area and to avoid putting restrictions on 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