Tab control - misbehaving

[teachman]

Hello,

I have multiple workbooks with the first worksheet of each having data entry fields and corresponding labels to the left of the field. I want to enable Tab control so the cursor skips the label fields. I also want to protect the label fields from being inadvertently changed by the user.

For the tab control I have a worksheet_activate macro. For the label protection I have locked each label field and unlocked each data entry field and enabled Protect worksheet. I want to enable both when the workbook opens and/or when the user moves to another worksheet and back again to the first worksheet to enter more data.

I have been able to do each action in separate macros, with the Tab control being the first macro. I used the following code to accomplish this.

Private Sub Worksheet_Activate()
' Setup Predefined Cursor Path (PCP)
Range("D5,N5,Y5,G6,K6,P6,V6,AC7,F8,S8,AB8,E9,E10,F11,Q11,AH11,E13,K13,R13,AA13,AI13,F14,M14,M15,Q15,U15,Y15,AG15,K17,O17,T17,X17,AB17,I18,S18,I19,S19,J20,W20,H22,R22,E23,H24,M24,I25,I26,I27,O27,G29,I49,H51,Q51").Select
End Sub

And the following code for the section action.

Private Sub Worksheet_Activate()
' Check the trigger field.
If Worksheets("FrontDES").Range("C1") = "Spreadsheet" Then
    ' Protect Front worksheet - no password
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

' Unprotect Front worksheet - no password
If Worksheets("FrontDES").Range("C1") <> "Spreadsheet" Then
    ActiveSheet.Unprotect
End If
End Sub

Since both actions are to happen when the worksheet opens I put the two macros together as shown in the following code.

Private Sub Worksheet_Activate()
' Setup Predefined Cursor Path (PCP)
    Range("D5,N5,Y5,G6,K6,P6,V6,AC7,F8,S8,AB8,E9,E10,F11,Q11,AH11,E13,K13,R13,AA13,AI13,F14,M14,M15,Q15,U15,Y15,AG15,K17,O17,T17,X17,AB17,I18,S18,I19,S19,J20,W20,H22,R22,E23,H24,M24,I25,I26,I27,O27,G29,I49,H51,Q51").Select
'
' Protect Front worksheet non-data fields if a digital pen was not used
'
If Worksheets("FrontDES").Range("C1") = "Spreadsheet" Then
'
' Protect Front worksheet - no password
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

' Unprotect Front worksheet - no password
If Worksheets("FrontDES").Range("C1") <> "Spreadsheet" Then
    ActiveSheet.Unprotect
End If
End Sub

So, the expected behavior is when a workbook is opened with the first worksheet opened the Tab control code highlights the data entry fields as designed. And, the trigger works, protecting the worksheet. The problem is sometimes the expected behavior doesn't happen. Instead of tabbing from one highlighted data entry field to the next data entry field as defined, the Tab turns off the cursor control and goes to the next field, instead of the next data entry field.

The frustrating part, and the reason for this question, is that in some workbooks this works and in others it doesn't work as it should. I have copied the macro from a successful workbook to one that didn't work as designed (retaining the list of data entry fields). The Tab control still leaves the list of data entry fields when the Tab key is pressed instead of moving to the next data entry field.

I would much appreciate an explanation for why this misbehavior is happening and how to correct it.

Thanks,

George

 

[Jerry Sullivan]

Hi George,

When the sheet is activated, that code Selects the cells in your Predefined Cursor Path (PCP) and Protects the worksheet; however it doesn't do anything to change the Locked property of the Cells on that Sheet.

To get the desired behavior using Worksheet Protection, you will need to Lock all the Cells on the Sheet and Unlock the Cells in your PCP.

The inconsistent results you've observed may due to the code working on sheets that already had the correct cells locked, but not working on sheets that did not previously have the correct cells locked.

If you add code to Lock/Unlock the correct cells, the Tabbing action should limit movement to the Locked Cells; however it won't necessarily follow the order of the cells that are listed in the PCP. Instead the Tabbing will follow the default sequence for Tabbing based on Options selected. That might work fine for you provided the desired sequence follows a pattern like Left to Right across unlocked cells in a Row then down to the next Row.

You might find the code examples in this thread helpful...

http://www.mrexcel.com/forum/excel-questions/739188-visual-basic-applications-code-tab-order.html