如何获得vba循环结果填充组合框？ [英] How do I get vba loop result to populate a combobox?

问题描述

问题：我需要在活动工作簿中搜索工作表列表，并返回具有值与搜索输入匹配的单元格的每个工作表的名称。这些工作表的名称需要再填充一个带有重复的用户窗体组合框。

Problem: I need to search a list of worksheets in the active workbook and return the name of every worksheet that has a cell whose value matches a search input. The names of these worksheets need to then populate a userform combobox with duplicates.

部分解决方案：我已经能够反向工程一段代码，以上。但是，工作表名称当前填充了具有重复的msgbox。我将如何使这个结果填充一个组合框？

Partial Solution: I've been able to reverse-engineer a piece of code that does most of the above. However, the worksheet names currently populate a msgbox with duplication. How would I make this result populate a combobox instead?

我一直在试验输出到集合以及将结果写入一个新的工作表，但这些选项仍然处于概念阶段，因此我没有代码可以发布。

I've been experimenting with outputting to a collection as well as writing results to a new worksheet, but these option are still in the conceptual phase, so I have no code to post.

UPDATE（一些代码）：

UPDATE (some code):

Public Sub FindDate()
'find date data on all sheets

Dim ws As Worksheet
Dim rngFind As Range
Dim myDate As String
Dim firstAddress As String
Dim addressStr As String
Dim findNum As Integer
Dim sheetArray(299) As Integer
Dim arrayIndex As Integer

myDate = InputBox("Enter date to find")

If myDate = "" Then Exit Sub

For Each ws In ActiveWorkbook.Worksheets
    'Do not search the following sheets
    With ws
        If ws.Name = "CM Chapters" Then GoTo myNext
        If ws.Name = "CM Codes" Then GoTo myNext
        If ws.Name = "PCS Categories" Then GoTo myNext
        If ws.Name = "PCS Chapters" Then GoTo myNext
        If ws.Name = "PCS Code" Then GoTo myNext

    Set rngFind = .Columns(41).Find(what:=myDate, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

        If Not rngFind Is Nothing Then
        firstAddress = rngFind.Address

    Do
        findNum = findNum + 1
        addressStr = addressStr & .Name & vbCrLf

''''Original working code    
'            addressStr = addressStr & .Name & " " & rngFind.Address & vbCrLf
''''Modified to remove excess text                              

Set rngFind = .Columns(41).FindNext(rngFind)

    Loop While Not rngFind Is Nothing And rngFind.Address <> firstAddress
End If

myNext:
    End With

    Next ws

        If Len(addressStr) Then
''''    Original working code
'                MsgBox "Found: "" & myDate & "" " & findNum & " times." & vbCr & _
'                addressStr, vbOKOnly, myDate & " found in these cells"
''''    Modified to to remove excess text

            MsgBox vbCr & addressStr
        Else:
            MsgBox "Unable to find " & myDate & " in this workbook.", vbExclamation
        End If

End Sub

推荐答案

尝试此操作

Do
    findNum = findNum + 1

    addressStr = addressStr & .Name
    ComboBox1.AddItem addressStr 'replace ComboBox1 with your ComboBox name
    addressStr = addressStr & vbCrLf ' if you still want to add the Line feed                            

    Set rngFind = .Columns(41).FindNext(rngFind)

Loop While Not rngFind Is Nothing And rngFind.Address <> firstAddress

这篇关于如何获得vba循环结果填充组合框？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！