vba 我可以将 Excel ComboBox 设置为具有默认值吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31685120/
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
Can I set an Excel ComboBox to have a default value?
提问by jkupczak
My ComboBox (named ddDatabase) can have either one of two values. These two values are pulled from a table in a separate worksheet. When I open the Excel file, however, neither of the two values are selected. Instead the ComboBox appears empty. Is there any way to explicitly set a specific value to be the default?
我的 ComboBox（名为ddDatabase）可以有两个值之一。这两个值是从单独工作表中的表中提取的。然而，当我打开 Excel 文件时，这两个值都没有被选中。相反，组合框显示为空。有什么方法可以将特定值显式设置为默认值？
After some further testing, it appears that if the Excel file is saved with the first item of the ComboBox selected, the next time the file opens it will default to blank. Selecting any other value in the ComboBox other than the first will preserve the selection after you save, close, and re-open.
经过一些进一步的测试，看起来如果 Excel 文件是在选择 ComboBox 的第一项的情况下保存的，那么下次打开文件时它将默认为空白。在 ComboBox 中选择第一个以外的任何其他值将在您保存、关闭和重新打开后保留选择。
I've tried to use VBA to solve this issue by setting the value when the workbook is opened using this code:
我尝试使用 VBA 通过在使用以下代码打开工作簿时设置值来解决此问题：
Sub Workbook_Activate()
Dim ddDatabase As DropDown
Set ddDatabase = ActiveSheet.DropDowns("ddDatabase")
ddDatabase.Value = 1
End Sub
Unfortunately, it throws the following error:
不幸的是，它抛出以下错误：
unable to set the value property of the dropdown class
unable to set the value property of the dropdown class
Is there a solution to this?
这个问题有方法解决吗？
回答by David G
Use this:
用这个：
.ComboBox1.Text = .ComboBox1.List(1) 'Change the number to the value you want as default.
'If you want to default to something like 'select a value' write it as a string
You have to specify the value yourself at creation (or on workbook open, or form open if it is in a form).
您必须在创建时自己指定值（或在工作簿打开时，或在表单中打开时）。
Edit:
编辑：
You might want to change it to a validation list if that's more flexible for you:
如果这对您来说更灵活，您可能希望将其更改为验证列表：
With f_overview.Range("cell_act").Validation 'Change this to something like Sheets("Sheet1").Range("A1").Validation
.delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Acteurs!$E:$E" 'Change this to "=Sheet1!$A:$A"
End With
The first commented line is for the emplacement of the list, the second one is your source values (your two values).
第一个注释行用于列表的位置，第二个是您的源值（您的两个值）。
回答by MatthewD
You can manually set it. Maybe do it on the workbook_open event.
您可以手动设置它。也许在 workbook_open 事件上做。
ComboBox1.Text = "SomeText"
回答by hastings
Make your default value the first option in the drop down.
将您的默认值设为下拉列表中的第一个选项。
With ActiveSheet.Shapes("ddDatabase").ControlFormat
.value = 1
End With
回答by Arin
When I tried it in Excel 2013, it was as simple as selecting either of the two items in that ComboBox and then saving the workbook. Next time I opened it, the one I selected showed up as the "default".
当我在 Excel 2013 中尝试时，它就像选择该 ComboBox 中的两个项目之一然后保存工作簿一样简单。下次我打开它时，我选择的那个显示为“默认”。
It even worked when I set one of the two source cells to =NOW()(so that the displayed value would be different next time I opened it), then selected that and saved it. Next time I opened, it still had the (updated) value of =NOW()displayed in the ComboBox.
当我将两个源单元格之一设置为=NOW()（以便下次打开它时显示的值会有所不同）时，它甚至可以工作，然后选择并保存它。下次我打开时，它仍然=NOW()在 ComboBox中显示了（更新的）值。
Unless you need a pure-VBA solution?
除非您需要纯 VBA 解决方案？
