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

View products that this article applies to.

This article was previously published under Q213741

Symptoms

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

↑ Back to the top

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 feature.
-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.

The TAB key and arrow keys do not function properly because their functionality is determined by the unlocked cell to the right or below the active cell, and these cells are outside the restricted scroll area.

↑ Back to the top

Workaround

Microsoft provides programming examples 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
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:

Sub Set_Restrictions()
    Dim myArea As Object
    Dim counter As Integer
    Range("B5:C10").Locked = False
    Range("C14:D16").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

To reset the scroll area and 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

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

↑ Back to the top

References

For more information about the Locked property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type locked in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top