Excel: protected sheet TAB question 2

[DajOne]

Other than vertical or horizontal, is there a way to specify, in a protected worksheet, the sequence of cells accessed with the TAB key?

Example. Cell A2 B3 A4 B5 are unprotected for the user to enter data, the rest of the spreadsheet is protected. In an horizontal mode, the TAB sequence will be A2, A4, B3, B5. How can I make the TAB jump from lets say B5, B3, A2, A4 in that order?

Thanks

 

[combo]

I was thinking how to avoid switching sheets and make it all working after changing sheet name and position, selecting other workbooks. The code bocame more complicated (below), but first some explanations. I used code names for the whole project and worksheet, they can be found:

- for VBA project: in VBE Project Explorer window, select the 'VBAProject(WorkbookName.xls)' item. In the properties window (if not visible, display it: View>Properties Window) there is only one property (Name), by default VBAProject. If you like to change it, do the same in 'SwitchTabMacro' and 'InitIDs' procedures

- for worksheet: this is the text that you can see in Project Explorer between sheet's icon and text in brackets. This property is again (Name), at the top in properties window. It can also be changed. Depending on what is here, change 'Sheet1' in 'SwitchTabMacro' and 'InitIDs' procedures to real sheet's code name in your project.

Please note that code name does not accept spaces.

As we respond to events, the location of code is very important. The previous one handling proc for given worksheet's events was located in this sheet's module (NB., the error info indicates that it was in the other place).

As we now need some workbook events, it is necessary to change the location of part of the code (no change in standard module). To get workbook module, double-click ThisWorkbook icon in Project Explorer, you should get blank one. The code here (I limited actions to two cells only, A1 and B5, to shrink the posted code, use your original data set instead):

Private Sub Workbook_Activate()
Call SwitchTabMacro(ActiveSheet)
End Sub

Private Sub Workbook_Deactivate()
Call SwitchTabMacro(ActiveSheet, True)
End Sub

Private Sub Workbook_Open()
Call InitIDs
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call SwitchTabMacro(ActiveSheet)
End Sub

Private Sub SwitchTabMacro(Sh As Object, Optional bForceOFF As Boolean = False)
If bForceOFF Or Not ActiveSheet Is VBAProject.Sheet1 Then
    Application.OnKey "{TAB}", ""
Else
    Application.OnKey "{TAB}", "GoToNextCell"
End If
End Sub

Private Sub InitIDs()
With VBAProject.Sheet1
    .Range("A1").ID = "B5"
    .Range("B5").ID = "A1"
End With
End Sub

The TAB key works only for cells with ID defined and <> "". If you like to jump into marked cell when you are outside, modify the 'GoToNextCell' procedure:

Public Sub GoToNextCell()
If ActiveCell.ID <> "" Then
    Range(ActiveCell.ID).Select
Else
    Range("E4").Select
End If
End Sub

Finally we do not need the old code in the worksheet's module, so clear it. Hope that all that will work for you.

combo

 

[DajOne]

Sorry guys, I did not forget, got side tracked at work on other stuff. Will bring this at home and try it over the weekend...

Thanks!!!!

 

[DajOne]

Hard to believe but this thing is in my list to do.. Got dragged onto another project, what's new...