Locked Cells & List Boxes in Template

[Guest]

I've set up a protected Excel template with locked cells so users can tab to
the cells that need to be filled in. I have five columns in which they
wanted to have a drop down list so they can select the appropriate heading
for each column. Once you tab to the last of these columns with the drop
down list, the next tab takes you to the first of these five columns instead
of going to the next cell to be filled in. You can click into the next
unlocked cell, but I'd rather have the users still be able to tab to the next
unlocked cell.

 

[Gord Dibben]

That's the problem with using the protection feature to Tab to unlocked cells.

The order must be left to right and top to bottom.

There are a couple of other methods.

See Bob Phillips' site for another using a named range.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

Also, there is event code which can take you through cells in a pre-defined
order.

Post back for code if you would like to try that.


Gord Dibben MS Excel MVP

 

[Guest]

I'm not familiar with event code, but it's worth a try. I think the other
option is too limited. Thanks again!

 

[Guest]

As my cells were still left to right, top to bottom, I thought it might be
because I had merged cells by row (i.e. A2, A3, A4, A5) to allow for the
height of the column headings. I took out the merged cells, made one row for
all my column headings and adjusted the height to the maximum I needed for
the longest wrapped heading. The tabs now go to the next unlocked cell after
the drop down list cells. I have other merged cells within the template
which worked, but those are merged by column (i.e. A2, B2, C2). Thanks
again! Have a great weekend!

 

[Gord Dibben]

Limiting in which way? Not enough cells in the named range?

45 seems like quite a few.

But......expand on this code if you wish.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A3", "B5", "C2", "A10", "B1", "D4", "E6", "E9", "D14")

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i

End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord

 

[Gord Dibben]

Ahhh so.

The old "merged cells" rises once again to smite a blow to any who employ this
severely limiting feature.


Gord