我可以将Excel ComboBox设置为默认值吗? [英] Can I set an Excel ComboBox to have a default value?

问题描述

我的ComboBox(命名为ddDatabase)可以具有两个值之一.这两个值是从单独工作表中的表中提取的.但是，当我打开Excel文件时，两个值均未选中.而是组合框显示为空.有什么方法可以将特定值显式设置为默认值?

经过一些进一步的测试后，如果保存了Excel文件并选择了ComboBox的第一项，则下次打开该文件时，它将默认为空白.保存，关闭并重新打开后，在组合框中选择除第一个以外的任何其他值将保留选择.

我尝试使用VBA通过使用以下代码打开工作簿时设置值来解决此问题:

Sub Workbook_Activate()

    Dim ddDatabase As DropDown
    Set ddDatabase = ActiveSheet.DropDowns("ddDatabase")
    ddDatabase.Value = 1

End Sub

不幸的是，它引发以下错误:

unable to set the value property of the dropdown class

对此有解决方案吗?

解决方案

使用此:

.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

您必须在创建时自行指定值(或在打开工作簿时，或者在表单打开(如果它在表单中)的时候).

如果您更灵活，则可能需要将其更改为验证列表:

    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$4:$E$23" 'Change this to "=Sheet1!$A$1:$A$2"
    End With

第一行注释是用于列表的位置，第二行是您的源值(您的两个值).

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?

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.

I've tried to use VBA to solve this issue by setting the value when the workbook is opened using this code:

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

Is there a solution to this?

解决方案

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$4:$E$23" 'Change this to "=Sheet1!$A$1:$A$2"
    End With

The first commented line is for the emplacement of the list, the second one is your source values (your two values).

这篇关于我可以将Excel ComboBox设置为默认值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！