选择选项后，VBA Excel 组合框不显示值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14944261/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel combobox not displaying the value after selecting option
提问by disposablereviewer
Private Sub ComboBox1_DropButtonClick()
If ComboBox1.ListCount > 0 Then
ActiveSheet.ComboBox1.Clear
End If
For N = 1 To ActiveWorkbook.Sheets.Count - 1
ComboBox1.AddItem ActiveWorkbook.Sheets(N).Name
Next N
End Sub
I'm new to VBA so please bear with me. I may not be doing this the best way to begin with.
我是 VBA 新手，所以请耐心等待。我可能不是最好的开始方式。
The code is taking the names of each sheet in my workbook (with the exception of the last sheet) and adding them to a combobox list. At first, each time I clicked the drop down, all sheet names were being added again making the list continue to grow with every click. My remedy was to clear the combobox first on each click and repopulate.
该代码采用我的工作簿中每个工作表的名称（最后一个工作表除外）并将它们添加到组合框列表中。起初，每次我单击下拉菜单时，所有工作表名称都会再次添加，使列表随着每次单击而继续增长。我的补救措施是在每次点击时首先清除组合框并重新填充。
However, with the clear option being used, the value is not being displayed when making my selection. It displays fine when not using the clear option. Everything else still works, but I need it to show the selected value so users aren't confused.
但是，由于使用了 clear 选项，因此在进行选择时不会显示该值。不使用 clear 选项时显示正常。其他一切仍然有效，但我需要它来显示选定的值，以便用户不会感到困惑。
Is there a better way to accomplish what I need?
有没有更好的方法来完成我所需要的？
EDIT: If it matters, this is not in a user form, it's just a active x combobox located directly on a worksheet.
编辑：如果重要的话，这不是在用户表单中，它只是一个直接位于工作表上的活动 x 组合框。
回答by nemmy
Something like below would work. However, I'd question why you'd want to repopulate the combobox everytime someone clicks on it. Why not do it when the workbook opens or the worksheet is activated?
像下面这样的东西会起作用。但是，我会质疑为什么每次有人点击它时都想重新填充组合框。为什么不在工作簿打开或工作表激活时执行此操作？
Private Sub ComboBox1_DropButtonClick(ComboBox1 As ComboBox)
Dim strSelected As String
If ComboBox1.ListIndex > -1 Then
strSelected = ComboBox1.List(ComboBox1.ListIndex)
End If
If ComboBox1.ListCount > 0 Then
ActiveSheet.ComboBox1.Clear
End If
For N = 1 To ActiveWorkbook.Sheets.Count - 1
ComboBox1.AddItem ActiveWorkbook.Sheets(N).Name
If strSelected = ActiveWorkbook.Sheets(N).Name Then
ComboBox1.ListIndex = N - 1
End If
Next N
End Sub
回答by Peter Albert
this is a very strange behavior - but the DopButtonClick event is triggered again when you select the item in the list. Therefore, the value that was just assigned get cleared upon the .Clearin the second run.
这是一个非常奇怪的行为 - 但是当您选择列表中的项目时，会再次触发 DopButtonClick 事件。因此，刚刚分配的值.Clear在第二次运行时被清除。
This code fixes it:
此代码修复它：
Private Sub ComboBox1_DropButtonClick()
Dim strValue As String
Dim n As Integer
strValue = ComboBox1.Value
If ComboBox1.ListCount > 0 Then
ActiveSheet.ComboBox1.Clear
End If
For n = 1 To ActiveWorkbook.Sheets.Count - 1
ComboBox1.AddItem ActiveWorkbook.Sheets(n).Name
Next n
ComboBox1.Value = strValue
End Sub
回答by bluequasi
Very nice solution Peter. In my case I have a list of items that can change between two combobox runs. If the selected combobox item is not anymore in the combo list, at the next run, the line:
非常好的解决方案彼得。就我而言，我有一个可以在两次组合框运行之间更改的项目列表。如果选定的组合框项目不再出现在组合列表中，则在下次运行时，该行：
ComboBox1.Value = strValue
throws an error .
引发错误。
I've found that declaring a public index:
我发现声明一个公共索引：
Public nr As Integer
and making a count inside the combobox code in order to run .clear only once per button action makes this working independently of the list update:
并在组合框代码内进行计数，以便每个按钮操作仅运行一次 .clear 使其独立于列表更新工作：
Private Sub ComboBox1_DropButtonClick()
Dim n As Integer
If nr = 0 Then
ActiveSheet.ComboBox1.Clear
nr = 1
Else
nr = 0
End If
For n = 1 To ActiveWorkbook.Sheets.count - 1
ComboBox1.AddItem ActiveWorkbook.Sheets(n).Name
Next n
End Sub
