When you use a Visual Basic for Applications macro to cycle through
unlocked cells, 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.
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.
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.
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
(https://partner.microsoft.com/global/30000104)
Microsoft Advisory Services -
http://support.microsoft.com/gp/advisoryservice
(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
(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
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article.
For more information about the Locked property, from the Visual Basic
Editor, click the Office Assistant, type
locked property, click Search,
and then click to view "Locked Property (Excel 97)."
NOTE: If the Assistant is hidden, click the Office Assistant button on the
Standard toolbar. If the Assistant is not able to answer your query, please
see the following article in the Microsoft Knowledge Base:
176476Â
(http://kbalertz.com/Feedback.aspx?kbNumber=176476/EN-US/
)
OFF: Office Assistant Not Answering Visual Basic Questions