Excel VBA:使用下拉列表显示表数据 [英] Excel VBA: Displaying table data using drop down list

问题描述

我是VBA的初学者.我在Excel中有一个工作表，其中创建每个月的表格以计算每个月的工时总数.这些表位于工作表中的不同列中.该表中的数据是在每个月创建的各个工作表中使用手动输入的数据填充的.

I am a beginner in VBA. I have a worksheet in Excel where tables for each individual month is created to calculate the total amount of manhours for each month. The tables are located in different columns in the worksheet. The data in this table is populated using manually inputted data in respective sheets created for each month.

当前，这是手动完成的，列表越来越长.我想通过创建一个下拉列表过滤器并从每个月和每年检索相应的数据并以日历的形式显示来改进此情况.谁能指导我如何做到这一点?

Currently, this is being done manually and the list is getting very long. I would like to improve on this by creating a dropdown list filter and retrieving the respective data from each month and year and displaying it in the form of a calendar instead. Can anyone guide me on how this can be done?

这是我要编译的工作表的示例:

This is an example of the worksheet I want to compile:

我希望它看起来像这样，其中数据的数据将根据我从各个月份检索到的数据而变化.月份和年份将以下拉列表的形式出现.

I want it to look something like this where the data for the data will change according to the data I retrieve from the respective months. The months and years will be in the form of drop down list.

该工作簿将每天进行更新.涉及的工作表的名称将采用以下格式命名:19年7月，19年8月，20年9月等.随着时间的推移，将会创建更多的工作表.

The workbook will be updated on a daily basis. The names of the worksheets involved will be named in a format like this: Jul'19, Aug'19, Sept'20, etc. There will be more worksheets created over time.

推荐答案

请创建一个表单，然后将下一个代码放入其模块中:

Please, create a form and place the next code in its module:

Option Explicit

Private Sub UserForm_Initialize()
    Dim sh As Worksheet, wb As Workbook
    
    Set wb = ActiveWorkbook
    For Each sh In wb.Sheets
        Me.cbMonth.AddItem sh.Name
    Next sh
End Sub

Private Sub cbMonth_Change()
  Dim wb As Workbook, sh As Worksheet, Tbl As ListObject
  Set wb = ActiveWorkbook
  Set sh = wb.Sheets(Me.cbMonth.Value)
  Set Tbl = sh.ListObjects(1)
  arr = Tbl.Range.Value
   With Me.ListBox1
        .ColumnCount = UBound(arr, 2)
        .ColumnWidths = "40;25;22;23;22;22;22;48;40"
        .list = arr
   End With
End Sub

因此，您必须放置一个名为 cbMonth 的组合框和一个名为'ListBox1`的列表框.

So, you must place a combo box named cbMonth and a list box named 'ListBox1`.

在 Initialize 窗体事件上，组合框将填充工作表名称.

On the form Initialize event the combo box is populated with the sheets name.

在组合框 Change 事件上，"ListBox1"填充了所选工作表名称的第一个表格范围.因此，在每个工作表中应该只存在一个表，或者，如果有更多表，则必须有一个表.

On the combo box Change event the 'ListBox1` is populated with the first table range of the selected sheet name. So, in each sheet should exist only one table, or, if more tables, the necessary one must be the first.

您还可以使用其 DblClick 事件在列表框中输入数据.系统将询问用户输入数据的日期，然后也可以更新工作表...

You will also be able to input data in the list box, using its DblClick event. The user will be asked about the day where the data to be inputted and then, the sheet can be updated, too...

这篇关于Excel VBA:使用下拉列表显示表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！