Tabbing rules in a locked sheet

[stevebigtk]

Hi there

I'm trying to set specific tabbing rules in a worksheet. The below code (kindly provided by mikerickson) works fine when the sheet is unlocked, but when it's locked, it will tab back to a merged and necessarily unlocked cell. I can't work out how to get round this! I need the merged cells to remain merged and the sheet to remain locked. Below is the code and a link to the sheet...

http://www.filedropper.com/tabbingissue

<code style="border: 0px; font-family: 'Segoe UI', Arial, sans-serif; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased;">Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Static NextCell As Range    If NextCell Is Nothing Then        If Target.Cells.Count = 1 And Not (Application.Intersect(Target, Range("L6,L12,L18,L24,L30")) Is Nothing) Then            Set NextCell = Target.Offset(2, -8)        End If    Else        Application.EnableEvents = False        NextCell.Select        Set NextCell = Nothing    End If    Application.EnableEvents = TrueEnd Sub</code></pre>

Any help very much appreciated!

Thanks

 

[dmt32]

Hi welcome to the board.

Have a look here:http://www.mrexcel.com/forum/excel-questions/726338-tab-order-excel-form-2.html#post3576607
and look for the 11th post.

Code is in two parts - worksheet code (which you need to delete old code) & then place in the sheets code page for your table. & module code.

The module code is a modified version of one I thought OP had most likely downloaded from another site but suggested using it another way from the published version - it may be of help to you

You will need to adjust the TabOrder array with the ranges and their tab order.

Hope helpful

Dave

 

[stevebigtk]

Wow! Cheers Dave, this is perfect. I've been a regular poster on different Excel forums and have been waiting for days without any luck, and on here have the solution in 40 minutes.

Thanks again

Steve

 

[dmt32]

Wow! Cheers Dave, this is perfect. I've been a regular poster on different Excel forums and have been waiting for days without any luck, and on here have the solution in 40 minutes.

Thanks again

Steve

Thanks for feedback - much appreciated. You should find that there are many excellent </SPAN>contributors on this board.

I should have added that the amendments I made to anothers existing code & change of approach I originally offered was just a development idea to previous OP & not a fully tested solution & will need further work but you have probably figured this for yourself. At moment, only Tab key is set to the TabRange procedure but you can add Enter key if needed.

Dave

 

[stevebigtk]

Hi Dave

The solution works perfectly in the worksheet, but it's affecting the tabbing of other worksheets! Do you know how I make it so that it will only affect the sheet it's designed for?

Thanks
Steve

 

[dmt32]

Hi Dave

The solution works perfectly in the worksheet, but it's affecting the tabbing of other worksheets! Do you know how I make it so that it will only affect the sheet it's designed for?

Thanks
Steve

Sounds like you have omitted the following procedure from your worksheets code page:

</SPAN>
Private Sub Worksheet_Deactivate()</SPAN>
Application.OnKey "{TAB}"</SPAN>
End Sub

</SPAN>
This code is required to reset the TAB key when you leave the worksheet

You may however, want to have a look at this thread:http://www.mrexcel.com/forum/excel-questions/739188-visual-basic-applications-code-tab-order.html</SPAN>

Post 9 onwwards offers a cleaner solution which you may find useful.

Dave</SPAN>

 

[stevebigtk]

Thanks Dave. That code you mention is in the sheet already so not sure why it's misbehaving. Will have a look into it.
Cheers

Steve