Next Cell Selection
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Next Cell Selection

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Next Cell Selection

     
    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.

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,178
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Next Cell Selection

    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

  3. #3
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,048
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Next Cell Selection

    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)

    Code:
    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:

    Code:
    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
    Last edited by dmt32; Aug 6th, 2017 at 03:51 AM.

  4. #4
    New Member
    Join Date
    Oct 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Next Cell Selection

    Quote Originally Posted by dmt32 View Post
    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)

    Code:
    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:

    Code:
    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.

  5. #5
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,048
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Next Cell Selection

    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

  6. #6
    New Member
    Join Date
    Oct 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Next Cell Selection

    Quote Originally Posted by dmt32 View Post
    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

  7. #7
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,048
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Next Cell Selection

    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

    Code:
    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:

    Code:
    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
    Last edited by dmt32; Aug 6th, 2017 at 08:22 AM.

  8. #8
    New Member
    Join Date
    Oct 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Next Cell Selection

    Quote Originally Posted by dmt32 View Post
    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

    Code:
    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:

    Code:
    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.

  9. #9
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,048
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Next Cell Selection

    Quote Originally Posted by Stealth2016 View Post
    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

  10. #10
    New Member
    Join Date
    Oct 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Next Cell Selection

      
    Quote Originally Posted by dmt32 View Post
    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.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com