excel 中vb组合框_Excel表中的数据验证组合框

excel 中vb组合框

There are instructions on my Contextures website for using a combo box with data validation cells. Click on a cell that contains a data validation list, and the combo box appears.

我的Contextures网站上有使用组合框和数据验证单元格的说明。 单击包含数据验证列表的单元格，然后出现组合框。

It takes a bit of programming, but has several advantages over a data validation list drop down:

它需要一些编程，但是与数据验证列表下拉列表相比有几个优点：

• a combo box's font size can be set
  可以设置组合框的字体大小
• more than 8 rows can be displayed
  可以显示8行以上
• autocomplete can be enabled
  可以启用自动完成功能

退出组合框 (Exit the Combo Box)

In the first version of the data validation combo box, you had to double-click on a cell, to make the combo box appear. In the next version, you simply clicked on a cell with a data validation list, and the combo box appeared.

在数据验证组合框的第一个版本中，您必须双击一个单元格以使组合框出现。 在下一版本中，您只需单击带有数据验证列表的单元格，就会出现组合框。

Then, Ted Lanham suggested code that would allow you to exit the combo box by pressing the Enter key or the Tab key, so you didn't even have to touch the mouse to make the combo box appear. The mind control version is still on the drawing board.

然后，Ted Lanham建议使用允许您通过按Enter键或Tab键退出组合框的代码，因此您甚至不必触摸鼠标即可显示组合框。 心智控制版本仍在绘图板上。

Here's Ted's original code, that moves one cell to the right, if the Tab key is pressed, and one cell down, if Enter is pressed, while the combo box is active.

这是Ted的原始代码，当组合框处于活动状态时，如果按下Tab键，则向右移动一个单元格；如果按下Enter键，则向右移动一个单元格。

'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub
'====================================

使用Excel表格 (Working with Excel Tables)

In Excel 2007, the Excel Table feature was added, replacing the List feature from Excel 2003. The last cell in the Excel Table has a handle that you can drag, to adjust the size of the table.

在Excel 2007中，添加了Excel Table功能，取代了Excel 2003中的List功能。ExcelTable中的最后一个单元格具有一个可以拖动的手柄，用于调整表格的大小。

The Enter key works the same in an Excel Table, always moving you to the cell below (unless you've changed your settings).

Enter键在Excel表中的工作原理相同，始终将您移到下面的单元格中(除非您更改了设置)。

However, the Tab key works a bit differently in an Excel Table:

但是，Tab键在Excel表中的工作方式略有不同：

• If you press the Tab key in the last column of an Excel table, you don't go to the right -- you go down one row, and to the first column of the table.
  如果您在Excel表格的最后一列中按Tab键，则不会转到右侧-您会向下一行进入表格的第一列。
• If you press the Tab key in the last cell of an Excel Table, a new row is added at the bottom of the table, and you move to the first column in the new row.
  如果您在Excel表格的最后一个单元格中按Tab键，则会在表格的底部添加新行，然后移至新行的第一列。

更改组合框代码 (Changing the Combo Box Code)

To accommodate for data validation combo boxes that are in Excel Tables, I've changed the code so it works the same as the regular Tab key.

为了适应Excel表中的数据验证组合框，我更改了代码，使其与常规的Tab键相同。

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
Dim tb As ListObject
Dim lCols As Long
Dim lCol As Long
Dim lRows As Long
Dim lRow As Long
Dim lColStart As Long
Dim lRowStart As Long
On Error Resume Next
Set tb = ActiveCell.ListObject
lCols = tb.ListColumns.Count
lCol = tb.ListColumns(lCols).Range.Column
lRows = tb.ListRows.Count
lRow = tb.ListRows(lRows).Range.Row
lColStart = tb.ListColumns(1).Range.Column
lRowStart = tb.ListRows(1).Range.Row - 1
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
  Case 9 'tab
    If ActiveCell.Column = lCol Then
      If ActiveCell.Row = lRow Then
        tb.Resize Range(Cells(lRowStart, lColStart), Cells(lRows + 2, 3))
      End If
      ActiveCell.Offset(1, -(lCol - lCols)).Activate
    Else
      ActiveCell.Offset(0, 1).Activate
    End If
  Case 13 'enter
    ActiveCell.Offset(1, 0).Activate
  Case Else
    'do nothing
End Select
End Sub

The Enter code hasn't changed -- just the Tab key code. The code tries to set a variable for the ListObject, then calculate the number of rows and columns in the table.

输入代码未更改-只是Tab键代码。 该代码尝试为ListObject设置一个变量，然后计算表中的行数和列数。

The heading row isn't counted in the ListRows, so the code adjusts for that.

标题行不计入ListRows中，因此代码对此进行了调整。

• If the active cell is in the last column, pressing Tab will take you to the first cell in the next row.
  如果活动单元格在最后一列，请按Tab键将带您到下一行的第一个单元格。
• If the active cell is in the last column and last row, pressing Tab will create a new row, and will take you to the first cell in the new row.
  如果活动单元格在最后一列和最后一行中，则按Tab键将创建一个新行，并将您带到新行中的第一个单元格。

下载样本文件 (Download the Sample File)

To see the code, and test the Tab key behaviour, you can download the data validation combo box for Excel Tables workbook.

若要查看代码并测试Tab键的行为，可以下载Excel Tables工作簿的数据验证组合框 。

The file is zipped, and you'll have to enable macros when you open the file in Excel. ____________

该文件已压缩，在Excel中打开文件时必须启用宏。 ____________

翻译自: https://contexturesblog.com/archives/2010/09/13/data-validation-combo-box-in-excel-table/

excel 中vb组合框