获取列表框的值以随工作表动态变化 [英] Getting the value of a listbox to dynamically change with worksheets

问题描述

当用户更改同一用户表单中另一个列表框的值时，如何获取用户表单中列表框的值以动态更改.给我带来麻烦的主要部分是 lstMonth.lstMonth 的值应等于月份，这与将显示在第一个列表框中的不同工作表中的数据相关.我将提供表格和数据的图片，以便让这一切变得更多.

How can I get the value of a listbox in a userform to dynamically change when the user changes the value of a another list box in the same userform. The main portion that is giving me trouble is lstMonth. The value of lstMonth should equal the months, which correlate to the data in different sheets that will show in the first listbox. I will provide a picture of the form and data so that this all makes more since.

代码

Option Explicit


Private Sub cmdCalc_Click()
    'declare variables and assign address to rngData variable
    Dim strId As String, intRow As Integer, intNumSales As Integer, curSales As Currency
    Dim rngData As Range
    Set rngData = Application.Workbooks("t13-ex-e4d.xls").Worksheets("jan").Range("a3").CurrentRegion
    'assign selected list box item to strId variable
    strId = lstId.Value
    'locate first occurrence of ID
    intRow = 2
    Do Until rngData.Cells(RowIndex:=intRow, columnindex:=4).Value = strId
        intRow = intRow + 1
    Loop
    'accumulate and count salesperson's sales, stop when loop encounters different ID
    Do While rngData.Cells(RowIndex:=intRow, columnindex:=4).Value = strId
        curSales = curSales + rngData.Cells(RowIndex:=intRow, columnindex:=3).Value
        intNumSales = intNumSales + 1
        intRow = intRow + 1
    Loop
    'display appropriate amount
    If optTotal.Value = True Then
        lblAnswer.Caption = Format(expression:=curSales, Format:="currency")
    Else
        lblAnswer.Caption = Format(expression:=curSales / intNumSales, Format:="currency")
    End If

End Sub

Private Sub cmdCancel_Click()
    'close custom dialog box
    Unload frmSalesCalc

End Sub


Private Sub UserForm_Initialize()

lstMonth.Value = Application.Workbooks("T13-EX-E4D.xls").ActiveSheet.Range("b3").CurrentRegion


End Sub

推荐答案

在打开的表单上用所有可用的表格填充月份列表

On the form open fill the month listbox with all avaible sheets

     ' Declare Current as a worksheet object variable.
     Dim Current As Worksheet

     ' Loop through all of the worksheets in the active workbook.
     For Each Current In Worksheets

        ' Insert your code here.
        ListBox_Month.AddItem Current.Name
     Next

如果点击月份列表，剩下的基本上是用所有可用的 id 填充另一个列表框.所以在 Listbox_Change 方法中你需要这样的东西:

The rest is basicaly to fill the other listbox if the month List is clicked, with all the avaible id's. So in the Listbox_Change Method you Need something like this:

Dim sales_ids as Variant 
sales_ids = UniquesFromRange(Worksheets(Listbox_Month.value).Range(D))

Function UniquesFromRange(rng As Range)
 Dim d As Object, c As Range, tmp
 Set d = CreateObject("scripting.dictionary")
 For Each c In rng.Cells
   tmp = Trim(c.Value)
   If Len(tmp) > 0 Then
        If Not d.Exists(tmp) Then d.Add tmp, 1
   End If
 Next c
 UniquesFromRange = d.keys
End Function

现在你有了所有的 id，把它们填在第二个列表框中，瞧，剩下的应该很清楚了，但如果你还有问题就问

Now you have all the id's, fill them in the second listbox, and voila, the rest should be clear, but just ask if you still have questions

这篇关于获取列表框的值以随工作表动态变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！