Next Cell Selection

[Stealth2016]

I am trying to write an excel spreadsheet Darts Score Book for my local Dart League and need to be able to control the next cell selection for data input over 3 rows of 14 columns for each team (Triples Game). That is 84 cells in total (B7:O9 and Z7:AM9). First entry being Cell B7 then Z7, B8, Z8, B9, Z9, C7, AA7 etc. Can this be done. I also need to do this for Doubles and Singles matches.

[oldbrewer]

maybe better to enter data in BA1 and BA2 and let cells B7 and Z7 etc etc pull the data from there

in fact if you enter data as a single column in BA each time you press enter you will go to the the next cell automatically

[dmt32]

Hi,
If happy to use some VBA in your workbook then the Tabbing solution I developed with Jerry Sullivan MVP should do what you want:

Place ALL following code in a STANDARD module (Alt+F11 > Insert > Module)

Public Const TabSheet As String = "YourSheetName"


 Sub SetOnkey(ByVal state As Integer)
 '  Ver 2 2014
 ' Authors Dave Timms (aka DMT32) and  Jerry Sullivan MVP


    If state = xlOn Then
        With Application
            .OnKey "{TAB}", "'TabOrder xlNext'"             'Tab key
            .OnKey "+{TAB}", "'TabOrder xlPrevious'"        'Shift + Tab Key
            .OnKey "~", "'TabOrder xlNext'"                 'Enter Key
            .OnKey "{RIGHT}", "'TabOrder xlNext'"           'Right Arrow Key
            .OnKey "{LEFT}", "'TabOrder xlPrevious'"        'Left Arrow Key
            .OnKey "{DOWN}", "do_nothing"
            .OnKey "{UP}", "do_nothing"
        End With
    Else
    'reset keys
        With Application
            .OnKey "{TAB}"
            .OnKey "~"
            .OnKey "{RIGHT}"
            .OnKey "{LEFT}"
            .OnKey "{DOWN}"
            .OnKey "{UP}"
        End With
    End If
End Sub


Sub do_nothing()
'nothing to do
End Sub


Sub TabOrder(ByVal Direction As XlSearchDirection)
'  Ver 2 2014
 ' Authors Dave Timms (aka DMT32) and  Jerry Sullivan MVP


    Dim m As Variant, i As Long


    On Error Resume Next
    m = Application.Match(ActiveCell.Address(0, 0), TabOrderArray, False)
    On Error GoTo exitsub
    
    'if activecell is not in Taborder array start at first cell
    If IsError(m) Then
        'goto first cell in array
        i = LBound(TabOrderArray)
    Else
        'get corresponding array index
        i = m + LBound(TabOrderArray) - 1
        'increment i value based on tab direction
        i = i + IIf(Direction = xlPrevious, -1, xlNext)
        'ensure stay within array bounds
        If i > UBound(TabOrderArray) Then i = LBound(TabOrderArray)
        If i < LBound(TabOrderArray) Then i = UBound(TabOrderArray)
    End If
    'select cell based on array element
    Application.EnableEvents = False
    Range(TabOrderArray(i)).Select
exitsub:
    Application.EnableEvents = True
End Sub


Function TabOrderArray() As Variant
    TabOrderArray = Array("B7", "Z7", "B8", "Z8", "B9", "Z9", "C7", "AA7")
End Function

You will need to make the following changes shown in RED for your project:

YourSheetName – enter the name of your worksheet e.g “Sheet1”
TabOrderArray – complete the tab order cell addresses in the TabOrderArray Function.

Place all following code In the Thisworkbook code page:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = TabSheet Then SetOnkey xlOn
End Sub


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = TabSheet Then SetOnkey xlOff
End Sub


Private Sub Workbook_WindowActivate(ByVal Wn As Window)
If ActiveSheet.Name = TabSheet Then SetOnkey xlOn
End Sub
 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
SetOnkey xlOff
End Sub

Solution is well developed and does not require sheet to be protected. It also allows users to navigate the required range in BOTH directions using Tab, Enter, Left & Right Arrows - You can also start from any cell specified in the array.


Hope Helpful

Dave

[Stealth2016]

Hi,
If happy to use some VBA in your workbook then the Tabbing solution I developed with Jerry Sullivan MVP should do what you want:

Place ALL following code in a STANDARD module (Alt+F11 > Insert > Module)

Public Const TabSheet As String = "YourSheetName"


 Sub SetOnkey(ByVal state As Integer)
 '  Ver 2 2014
 ' Authors Dave Timms (aka DMT32) and  Jerry Sullivan MVP


    If state = xlOn Then
        With Application
            .OnKey "{TAB}", "'TabOrder xlNext'"             'Tab key
            .OnKey "+{TAB}", "'TabOrder xlPrevious'"        'Shift + Tab Key
            .OnKey "~", "'TabOrder xlNext'"                 'Enter Key
            .OnKey "{RIGHT}", "'TabOrder xlNext'"           'Right Arrow Key
            .OnKey "{LEFT}", "'TabOrder xlPrevious'"        'Left Arrow Key
            .OnKey "{DOWN}", "do_nothing"
            .OnKey "{UP}", "do_nothing"
        End With
    Else
    'reset keys
        With Application
            .OnKey "{TAB}"
            .OnKey "~"
            .OnKey "{RIGHT}"
            .OnKey "{LEFT}"
            .OnKey "{DOWN}"
            .OnKey "{UP}"
        End With
    End If
End Sub


Sub do_nothing()
'nothing to do
End Sub


Sub TabOrder(ByVal Direction As XlSearchDirection)
'  Ver 2 2014
 ' Authors Dave Timms (aka DMT32) and  Jerry Sullivan MVP


    Dim m As Variant, i As Long


    On Error Resume Next
    m = Application.Match(ActiveCell.Address(0, 0), TabOrderArray, False)
    On Error GoTo exitsub
    
    'if activecell is not in Taborder array start at first cell
    If IsError(m) Then
        'goto first cell in array
        i = LBound(TabOrderArray)
    Else
        'get corresponding array index
        i = m + LBound(TabOrderArray) - 1
        'increment i value based on tab direction
        i = i + IIf(Direction = xlPrevious, -1, xlNext)
        'ensure stay within array bounds
        If i > UBound(TabOrderArray) Then i = LBound(TabOrderArray)
        If i < LBound(TabOrderArray) Then i = UBound(TabOrderArray)
    End If
    'select cell based on array element
    Application.EnableEvents = False
    Range(TabOrderArray(i)).Select
exitsub:
    Application.EnableEvents = True
End Sub


Function TabOrderArray() As Variant
    TabOrderArray = Array("B7", "Z7", "B8", "Z8", "B9", "Z9", "C7", "AA7")
End Function

You will need to make the following changes shown in RED for your project:

YourSheetName – enter the name of your worksheet e.g “Sheet1”
TabOrderArray – complete the tab order cell addresses in the TabOrderArray Function.

Place all following code In the Thisworkbook code page:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = TabSheet Then SetOnkey xlOn
End Sub


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = TabSheet Then SetOnkey xlOff
End Sub


Private Sub Workbook_WindowActivate(ByVal Wn As Window)
If ActiveSheet.Name = TabSheet Then SetOnkey xlOn
End Sub
 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
SetOnkey xlOff
End Sub

Solution is well developed and does not require sheet to be protected. It also allows users to navigate the required range in BOTH directions using Tab, Enter, Left & Right Arrows - You can also start from any cell specified in the array.


Hope Helpful

Dave

Thank you Dave for such a quick response. Do I have to put every cell reference in the array or eg. B7::AM52 I ask this as this score sheet has the following:
1 x Triple (3 players each side)
3 x Doubles (2 players each side)
6 x Singles (played over 3 games)
1 x Triple (lLast game of match - as per first Triple)

This equates to 30 Rows of 28 cells each row for a total of 840 cells.
Bill.

[dmt32]

Hi,
Solution was primarily designed for those who had input forms for their users they wanted to tab in a specific order – These forms would typically have around 30 input cells or less & you would specify all the cell addresses in the TabOrderArray Function in the required tabbing order.

Your requirement is clearly more involved but may be able to adapt solution to fit your requirement.

- If you want to tab through all the cells in the Range then one method to negate hard coding would be to output range to another sheet & the array read from that.

- If though, you want to tab your worksheet based on selection of Triple, Double, Single then placing the cell addresses for each option in another sheet & reading from it could be a solution.

I have tagged Jerry in on this thread & he may be able of offer some additional guidance but let me know what it is you want to do.

Dave

[Stealth2016]

Hi,
Solution was primarily designed for those who had input forms for their users they wanted to tab in a specific order – These forms would typically have around 30 input cells or less & you would specify all the cell addresses in the TabOrderArray Function in the required tabbing order.

Your requirement is clearly more involved but may be able to adapt solution to fit your requirement.

- If you want to tab through all the cells in the Range then one method to negate hard coding would be to output range to another sheet & the array read from that.

- If though, you want to tab your worksheet based on selection of Triple, Double, Single then placing the cell addresses for each option in another sheet & reading from it could be a solution.

I have tagged Jerry in on this thread & he may be able of offer some additional guidance but let me know what it is you want to do.

Dave

Thanks Dave, What I am trying to do is fully automate a Darts Score Sheet that has 2 teams playing the following games in a match. 1 x Triple (3 v 3) 3 Doubles (2 v 2) 6 Singles (1 v 1 in a best of 3 games) and then a second Triple (3 v 3). The Starting Cell in the first Triple can be either B7 or Z7 depending which player wins the nearest to the Bullseye. This method (Bullseye) is used to start every match. Each player is limited to 14 Throws at the dartboard hence the Cell Range from B7 to O9 and Z7 to AM9 in the first Triple. Doubles are from B15 to O16 and Z15 to AM16, B18 to O19 and Z18 to AM19, B21:O22 and Z21 to AM22. Singles go from B28 to O45 and Z28 to AM45. The second Triple from B50 to O52 and Z50 to AM52. We would like to tab to the next cell in sequence of data entry remembering that the starting cell can be either the B cell or Z cell.
Hope I haven't confused you too much. Bill

[dmt32]

Hi,
just a little - Not a dart player but does not take much to confuse me these days.

Don't have much time a moment but see if this is any help to you.

Place following in STANDARD module & run it

Sub BuildTabOrder()
    Dim cell As Range
    Dim r As Long
    Dim ws As Worksheet
    
    Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    ws.Name = "Tab Order"


    For Each cell In ws.Range("B7:AM52")
        r = r + 1
        ws.Cells(r, 1).Value = cell.Address(0, 0)
    Next cell
End Sub

This should create a worksheet with ALL cell addresses list in Column A

Replace the TabOrderArray Function with following updated version:

Function TabOrderArray() As Variant
        TabOrderArray = Application.Transpose(Worksheets("Tab Order").Range("A1:A1748").Value)
End Function

This update should read cell addresses in newly created sheet. You can change the Tab order addresses in the list to meet your specific requirement.

The TabOrder code allows you to start from any cell specified in the array - so just click on your start cell.

Probably more ideal, would be to have a specified array for each group but see how you get on with this.

Hope Helpful

Dave

[Stealth2016]

Hi,
just a little - Not a dart player but does not take much to confuse me these days.

Don't have much time a moment but see if this is any help to you.

Place following in STANDARD module & run it

Sub BuildTabOrder()
    Dim cell As Range
    Dim r As Long
    Dim ws As Worksheet
    
    Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    ws.Name = "Tab Order"


    For Each cell In ws.Range("B7:AM52")
        r = r + 1
        ws.Cells(r, 1).Value = cell.Address(0, 0)
    Next cell
End Sub

This should create a worksheet with ALL cell addresses list in Column A

Replace the TabOrderArray Function with following updated version:

Function TabOrderArray() As Variant
        TabOrderArray = Application.Transpose(Worksheets("Tab Order").Range("A1:A1748").Value)
End Function

This update should read cell addresses in newly created sheet. You can change the Tab order addresses in the list to meet your specific requirement.

The TabOrder code allows you to start from any cell specified in the array - so just click on your start cell.

Probably more ideal, would be to have a specified array for each group but see how you get on with this.

Hope Helpful

Dave

Thanks Dave, unfortunately the code you asked me to put in Thisworksheet doesn't compile. Bill.

[dmt32]

Thanks Dave, unfortunately the code you asked me to put in Thisworksheet doesn't compile. Bill.

What is the error you are are geeting?

Neither of those codes go in Thisworksheet - both should be in standard module

Dave

[Stealth2016]

What is the error you are are geeting?

Neither of those codes go in Thisworksheet - both should be in standard module

Dave

According to earlier posts it clearly states put all of this code into Thisworksheet. I get various errors but will try installing it into Standard Module and get back to you.