如何使用表格的前两列在Excel VBA中的用户窗体上创建两列组合框？

Question

我正在寻找生成一个用户窗体中的组合框，单击我的Excel电子表格上的命令按钮弹出。目的是从组合框中选择日期和时间（包括特定日期的多个条目的时间），然后单击表单上的“完成操作”命令按钮将值从“否”更改为“是”在所选条目的行末尾的单元格中。

---

从我迄今所做的研究，这是唯一的代码，我能够理解，足以进入，但我没有得到我所期待的显示效果。

Private Sub UserForm_Initialize() 
Dim v, e 
With Sheets("Phone Log").Range("B9:C76") 
    v = .Value 
End With 

With CreateObject("scripting.dictionary") 
    .comparemode = 1 
    For Each e In v 
     If Not .exists(e) Then .Add e, Nothing 
    Next 
    If .Count Then Me.combobox1.List = Application.Transpose(.keys) 
End With 


End Sub 

---

可有人请与代码帮助，使基准数据的两列显示对齐，以显示非空行的下拉列表中，然后把选定的行和更改多个单元格的值一旦用户点击“完成操作”命令按钮，右边的列？

---

我也将是开放的组合框/用户形式存在一个单独的分，这样我可以使用它作为只是选择行和运行从收集选择的日期，用户的命令按钮独立的功能。

TIA〜诱饵

Answer 1

我不知道这是否是你所需要的，但它应该工作（我试过）。

试图使它易于阅读：

Option Explicit 

Private Dic As New Scripting.dictionary 'needs a reference to "Microsoft scripting Runtime" in VBE>Tools>Reference 

Private Sub CommandButton1_Click() 
Dim h$ 
Dim i& 
h = Me.ComboBox1.Value 
If h = vbNullString Then Exit Sub 
i = Dic(h) 'converts to a Long in this case 
ThisWorkbook.Sheets("Phone Log").Cells(i, 3).Value2 = "YES" '3 is the "C" Column 
End Sub 

Private Sub UserForm_Initialize() 
Dim v() 'needs to be a variant 
Dim e$ 'is actually a string 
Dim i& 'that's a Long 

With ThisWorkbook.Sheets("Phone Log").Range("B1:B76") 'i only start at 1, because else i would need to be i-8 later on 
    v = .Value 'do NOT use .value2 in case of dates, or currencys 
End With 

'Set Dic = CreateObject("scripting.dictionary") 
With Dic 
    ' .comparemode = 1 'didn't find help on this one, not needed i guess 
    'For Each e In v 
    For i = 9 To 76 
     e = v(i, 1) 
     If e <> vbNullString Then If Not .exists(e) Then Dic(e) = i '.Add e,i 'or ThisWorkbook.Sheets("Phone Log").cells(i,2).address 
    Next i 
    If .Count > 0 Then Me.ComboBox1.List = Application.Transpose(.keys) 'i learned something here 
End With 

Erase v 
End Sub 

Private Sub UserForm_Terminate() 
Set Dic = Nothing 
End Sub 

编辑：（对不起，答案晚了，是beasy用我自己的东西）

上面的代码是一列组合框。

下面是关于如何建立一个3列组合框的例子（调整代码添加到您的个人需要）：

With Me.Combobox1 
    .ColumnCount = 3 
    .ColumnWidths = "44;70;30" 
    .ListWidth = 150 
    .Clear 
    .AddItem "*.XLSB" 
    .List(0, 1) = "Binary" 
    .List(0, 2) = "50" 

    .AddItem "*.XLSM" 
    .List(1, 1) = "Macro Enabled" 
    .List(1, 2) = "52" 

    .AddItem "*.XLS" 
    .List(2, 1) = "Excel97" 
    .List(2, 2) = "56" 

    .ListIndex = 0 
End With