Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locked Cells & List Boxes in Template
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locked Cells & List Boxes in Template
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 On Fri, 1 Jun 2007 13:14:01 -0700, frpkaren wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locked Cells & List Boxes in Template
I'm not familiar with event code, but it's worth a try. I think the other
option is too limited. Thanks again! "Gord Dibben" wrote: 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 On Fri, 1 Jun 2007 13:14:01 -0700, frpkaren wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locked Cells & List Boxes in Template
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" wrote: 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 On Fri, 1 Jun 2007 13:14:01 -0700, frpkaren wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locked Cells & List Boxes in Template
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 On Fri, 1 Jun 2007 14:48:01 -0700, frpkaren wrote: I'm not familiar with event code, but it's worth a try. I think the other option is too limited. Thanks again! "Gord Dibben" wrote: 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 On Fri, 1 Jun 2007 13:14:01 -0700, frpkaren wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locked Cells & List Boxes in Template
Ahhh so.
The old "merged cells" rises once again to smite a blow to any who employ this severely limiting feature. Gord On Fri, 1 Jun 2007 15:08:00 -0700, frpkaren wrote: 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" wrote: 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 On Fri, 1 Jun 2007 13:14:01 -0700, frpkaren wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste locked cells and unlocked cells in protected sheet | Excel Worksheet Functions | |||
HOW TO DELETE MULTIPLE TEXT BOXES FROM A TEMPLATE FILE | Excel Discussion (Misc queries) | |||
Selecting subsets using combo boxes or list boxes | Excel Discussion (Misc queries) | |||
Questions on combo boxes and list boxes. | New Users to Excel | |||
Put comments on a locked spreadsheet even though cells not locked | Excel Worksheet Functions |