vba 一定数量的列后，Excel 选项卡到新行

Excel tab to new line after certain amount of columns

提问by nevodj

I am wanting to set up an excel spreadsheet for data entry with a barcode scanner. The barcode scanner sends the barcode then a tab OR an enter key depending how its programmed.

我想使用条形码扫描仪设置用于数据输入的 Excel 电子表格。条码扫描器发送条码，然后根据其编程方式发送选项卡或回车键。

Basically I want to set up an excel sheet that we can scan 6 barcodes for each item, with the scanner tabbing to the next column each time, then when it reaches the 6th column the next tab will make it move to a new line for the next product.

基本上我想设置一个 excel 表格，我们可以为每个项目扫描 6 个条形码，扫描仪每次都切换到下一列，然后当它到达第 6 列时，下一个选项卡将移动到新的一行下一个产品。

I hope this makes sense. It can be done in MS word... e.g if you create a table with 6 columns and push tab 7 times it will move to the next row. I am wanting to do this in Excel. Thank you

我希望这是有道理的。它可以在 MS Word 中完成...例如，如果您创建一个具有 6 列的表格并按 Tab 键 7 次，它将移动到下一行。我想在 Excel 中执行此操作。谢谢

回答by Leniel Maccaferri

Well... after a lot of experimenting gathering pieces of code from a lot of places and then debugging I ended up with the following VBA macro. Hope it helps! :)

嗯……经过大量的实验，从很多地方收集代码片段然后调试，我最终得到了以下 VBA 宏。希望能帮助到你！:)

1. When TABor ENTERkey is pressed the Sub Worksheet_Changewill run.
2. It will check if it's column Fbeing left...
3. If true => insert new row and select first cell [A]nwhere n= row number.

1. 当按下TAB或ENTER键时，Sub Worksheet_Change将运行。
2. 它会检查它的列F是否被留下......
3. 如果为 true => 插入新行并选择第一个单元格[A]n，其中n= 行号。

VBA macro code

VBA 宏代码

Private Sub Worksheet_Change(ByVal Target As Range)

    'Do nothing if more than one cell is changed or content deleted   
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    'Column F
    If Target.Column = "6" Then

        'Insert new row bellow
        ActiveCell.EntireRow.Offset(1, 0).Insert

        'Select first cell of next row just inserted
        ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select

     End If

End Sub


Private Sub Workbook_Activate()
Application.OnKey "{TAB}", "Worksheet_Change" 'TAB key press
Application.OnKey "~", "Worksheet_Change" 'Keyboard ENTER press
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "{TAB}"
Application.OnKey "~"
End Sub

回答by David Zemens

The TAB or ENTER keys already trigger the SelectionChangeevent.

TAB 或 ENTER 键已触发该SelectionChange事件。

So, this might be a little tidier way of doing the same thing if you don't for some other reaason needto use the Changeevent instead of the SelectionChangeevent.

因此，如果您出于某些其他原因不需要使用Change事件而不是SelectionChange事件，那么这可能是一种更整洁的方式来做同样的事情。

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngLastColumn As Range

'Note: The {Tab} or {Enter} key triggers selectionChange event.'
' Modify the address of rngLastColumn as needed. It should be one column beyond
' the last column of your inputs, so if input use columns A:F, then it should
' be Range("G:G").

Set rngLastColumn = Range("G:G")
    If Not Intersect(Target, rngValidColumns.Columns(7)) Is Nothing Then
        'Insert a new row:'
        Target.EntireRow.Offset(1, 0).Insert

        'Select the first cell in the new row'
        cells(Target.Row + 1, 1).Select

    End If

End Sub

回答by P. O.

Maybe I'm missing something on the issue, but if you select your six columns and transform the selection with the "create list" command, then whenever you tab to the last cell of a line you'll automatically going to the next line. Furthermore, if you're at the last line, a new one will be created. IM not sure why you need a macro for that ?

也许我在这个问题上遗漏了一些东西，但是如果您选择六列并使用“创建列表”命令转换选择，那么每当您使用 Tab 键切换到一行的最后一个单元格时，您将自动转到下一行。此外，如果您在最后一行，则会创建一个新行。我不知道为什么你需要一个宏？