Why does tab not work when worksheet locked?

[StuLux]

Normally when you lock a worksheet you can use the tab key to move from one locked cell to the next. I have a workbook where this is true for all of the worksheets except one, where the tab key will move along the current row (to the next unlocked cell) but will not then go to the next row. When it gets to the last unlocked cell in any row it returns to the 1st unlocked cell in row 1.

I have various VBA routines for hiding/showing sheets, spell checking etc. but I cannot see any code that would affect the sheet in this way.

I have never come across this sort of strange behaviour before, can anybody help?

 

[just_jon]

Perhaps all cells except those on the one row are locked?

 

[StuLux]

Definitely not the case - there are several rows with several unlocked cells in each so this is not the cause. I can select any of the unlocked cells by clicking on them but as soon as I press enter the active cell becomes A1 (the 1st unlocked cell on the worksheet) or if I press Tab it moves along the row until the last locked cell and then goes back to A1.

I'm reasonably experienced in Excel and soem VBA and I've never come across this before! It cannot (presumably) be anything at a workbook level because the Tab key behaves as expected on the other locked sheets within the workbook.

 

[erik.van.geit]

StuLux,

always curious when I see this kinda problems
If you want you can email the (empty) workbook, I'll be glad to take a look.

kind regards,
Erik

 

[Oaktree]

Did you protect the sheet so that you do not allow users to select locked cells? (one of the options in tools --> protection --> protect sheet in Excel 2002 or later...)

 

[StuLux]

Yes, users cannot select locked cells (the main reason for locking the sheet) but I would still expect the tab key to move in a logical order between the unlocked cells and also, when in an unlocked cell and pressing Enter) I would not expect to be retunred to the top of the sheet (i.e. 1st unlocked cell) normally this would take you to the next unlocked cell.

 

[Oaktree]

I would check (again) that there are unlocked cells below the active row.

The behavior you're describing suggests that you have reached the last unlocked cell, then press Tab. That would cause Excel to return to the first unlocked cell, which is presumably A1 in your sheet.

 

[StuLux]

There are definitely unlocked cells below the current row, I've sent a copy of the workbook to Erik who has said he will take a look. Thanks for your help with this.

 

[erik.van.geit]

Hello, guys,
spent 2 hours on this

unlocked cells: A1 B1 B3 H3 and others
sheet protected
tab when in B1 brings me to A1

running this
MsgBox .Rows.Count
MsgBox .Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
you get 65536 and 24 !!!
there are entire columns conditional-formatted and the rows are invisible: so this seems like unnessecary formats
this is an explanation of your "undisered" filesize
deleting lines 25 to end decreases the size enormously
(check also the other sheets ...)

normally deleting rows and saving will change the "usedrange": not here ??
still bringing me to the last row !!??

perhaps sheet is somehow corrupt ?

this will give you some relief

Sub macro1()

With Sheets("Project Update").UsedRange
.Resize(.Rows.Count - 1, .Columns.Count).Copy
End With

Workbooks.Add
With ActiveSheet
.Paste
.EnableSelection = xlUnlockedCells
.Protect
End With

End Sub

strange thing is: just not copying one row solves it ??
still some formats to do but now the new sheet will tab correctly
just import again in your workbook

still better would be something like
(replace "With End With"-part of previous code with this)

With Sheets("Project Update")
LR = .Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
.Range(Cells(1, 1), Cells(LR, "Z")).Copy
End With

SUMMARY
I didn't find the real cause
tried a lot of options, such as
moving button
deleting validations
inserting column, rows
deleting rows
moving cells

workaround + little work to do
see code
or do it manually (select range copy to new sheet)
(I used code because trying several options)

this was hard
I hope this will get you out somewhat

kind regards,
Erik

 

[erik.van.geit]

got email
sheet was corrupt
seems to be solved