列表框根据选择生成辅助列表框 [英] Listbox generates secondary Listbox based on selection

问题描述

我对VBA还是很陌生，但是在工作表上有两个Activex列表框.在一个列表框上，我有销售人员名称(称为lstNames)，另一个列表框上有位置(lstSite).我要实现的目标如下:

I'm still pretty new to VBA, but I have two activex listboxes on a worksheet. On one listbox I have salesperson names (called lstNames) and the other listbox has locations (lstSite). What I'm trying to achieve is the following:

1. 从lstNames中选择名称
2. 从lstNames中进行选择会生成lstSite，其中包含与该名称相关联的一些位置的列表.

例如，如果我从lstNames列表框中选择Bob，则lstSites列表框将填充Bob的所有站点，例如NY，CO，CA，PA.如果我从lstNames中选择Susan，则lstSites将清除Bob的站点并添加Susan的TX，WA，OR等站点.

For example, if I select Bob from the lstNames listbox, then the lstSites listbox will populate all of Bob's sites like NY, CO, CA, PA. If I select Susan from lstNames, then lstSites will clear Bob's and add Susan's sites which are TX, WA, OR, etc.

lstNames和lstSite都来自同一工作表，其格式如下:

Both lstNames and lstSite pull from the same worksheet which is formated:

 Column 1: Names
 Column 2: Site

我一直在尝试执行Vlookup来将项目添加到列表框中，但是它不起作用.我知道我可以执行select case语句并手动添加站点，但是我想编写一个好的代码，这些代码将自动通过它们运行.

I've been trying to do a Vlookup to add items to the listbox, but it's not working. I know I can do a select case statement and add the sites manually, but I want to program a good code that will run through them automatically.

Select Case lstNames.Value
    Case "Bob"
    lstSites.AddItem WorksheetFunction.VLookup(lstDM.Text, wkbhcsfdata.Worksheets("Names+Sites").Range("Sites"), 1, False)
End Select

推荐答案

请尝试这样做是否有助于获取所需的范围地址.为此，我为Person使用命名范围，为Location使用另一个命名范围.变量strPerson可以替换为单元格值.

Try to if this helps get the range addresses you need. For this, I used named ranges for Persons and another for Locations. The variable strPerson could be replaced with a cell value.

Dim ws As Worksheet
Set ws = Worksheets("sheet2")

Dim rngPerson, rngLoc As Range
Set rngPerson = ws.Range("Person")
Set rngLoc = ws.Range("Location")

Dim iMatch, iCount As Integer
Dim strPerson As String
strPerson = "Bob"

iMatch = WorksheetFunction.Match(strPerson, rngPerson, 0)
iCount = WorksheetFunction.CountIf(rngPerson, strPerson)

Dim rngList As Range
Set rngList = ws.Range(rngLoc.Rows(iMatch), rngLoc.Rows(iMatch + iCount - 1))

一旦您具有rngList的动态范围，就可以使用类似以下内容的方法在rngList上循环:

Once you have the dynamic range of rngList, you can loop on rngList using something like:

For Each rw in rngList.rows
    [Your code to insert list items]
Next

所有这些都必须在ActiveX事件中.

All this would need to be in the ActiveX event.

将.Cells更改为.Rows

Changed .Cells to .Rows

这篇关于列表框根据选择生成辅助列表框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！