Tab order Problem "for the Brave" Protected sheet!!

[Darren]

Hi fellow comp slaves

I got some help from MD on this worksheet and it worked well. Different problem now, I need to tab down the sheet from ''D4" TO "E4" and down the sheet to "A36" and then up to "J4 tab to K4" and down to "G36" and then up to "P4 to Q4" an then to down again to the cell just right and below "Process" that is "S48" PLS NOTE PROTECTED SHEET !!!

Information about this sheet.....

this sheet is completed by reps from a care centre and there computer knowledge is very limited, however training is on the cards and this form is intended to make the completion a little easier by using tab to move down the page and then up to the top again. I have looked at tab index in a user form but have nooooooooooooooooo clue how to get the rest of the program that works to work in a user form.

http://www.vbaexpress.com/forum/showthread.php?t=1865

wont let me upload again ???? Above is the link to my file

God Bless




Darren

Ps thanks guys and ladies for the Help and support
please see attached zip file . Password for sheet "press space bar once" when prompted for password. This must be a protected sheet and reset to default once completed. The new info is automatically stored in "Database Building" This sheet builds new information about customers and can be used at a later time by just entering the telephone number.

[mdmackillop]

Hi Darren,
Try the following. Note that the MoveAfterEnter setting will be independent of the coded tab move, which will need to be reset in Offsets in the code below.

Place this in the Worksheet module

Option Explicit
Option Base 1 

Private Sub TabOrder()
'Set applicable sheet name
Const TabSheet = "Sheet1"
Dim Limit As Integer, NumPos As Integer
Dim NextPos As String, MyCel As String
Dim MyTO
'Set tab offset for other sheets
If ActiveSheet.Name <> TabSheet Then
ActiveCell.Offset(0, 1).Select
Exit Sub
End If
'Set your cells in desired order
MyTO = Array("A3", "A7", "B5", "C7")
Limit = UBound(MyTO)
MyCel = ActiveCell.Address(0, 0)
'Check for match in array
On Error GoTo LastLine
NumPos = Application.WorksheetFunction.Match(MyCel, MyTO, 0)
'Return to first cell
If NumPos = Limit Then
NextPos = MyTO(1)
Else
NextPos = MyTO(NumPos + 1)
End If
Range(NextPos).Activate
Exit Sub
LastLine:
'Set tab offset if named cell not found
ActiveCell.Offset(0, 1).Select
End Sub

Place this in the WorkBook module

Private Sub Workbook_Open()
Application.OnKey "{TAB}", "TabOrder"
End Sub

[Darren]

Hi MD

Thanks once again for the code. It seems to work well. I have started from the ground level again and made a new sheet and placed the tab order in first. Strange thing is even if the sheet is not protected it uses the same tabbing order ?

I have had to format the tabbed cells as "unlocked" so that data can be placed in them when the sheet is protected.

I would like you to list this worksheet maybe as an example of protected tabbing. We can remove the Haven's Name and Number ? I am sure there maybe others that have needed this sort of form.

password for protected document is "press space bar once"

I will now attempt to add all the other code to make it save to the database and so on..........

Thanks once again MD for your continued support

Kindest regards


Darren

[mdmackillop]

Hi Darren,

Dreamboat actually posted a neat function here for tabbing in a protected worksheet, which I will check to tidy up my own code, but is probably ideal for your purpose.

MD

http://www.vbaexpress.com/kb/getarticle.php?kb_id=209

[Darren]

Thanks MD


Once again.

Kindest regards


Darrren