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
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.
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
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)
You will need to make the following changes shown in RED for your project: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
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:
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.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
Hope Helpful
Dave
Last edited by dmt32; Aug 6th, 2017 at 03:51 AM.
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.
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
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
This should create a worksheet with ALL cell addresses list in Column ACode: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
Replace the TabOrderArray Function with following updated version:
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.Code:Function TabOrderArray() As Variant TabOrderArray = Application.Transpose(Worksheets("Tab Order").Range("A1:A1748").Value) End Function
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.
Like this thread? Share it with others