Excel VBA：从命令按钮调用 userForm 时不显示 ComboBox.Rowsource 值的动态范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6803846/
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
Excel VBA: Dynamic range for ComboBox.Rowsource values not displayed when userForm called from commandbutton
提问by Kurt
The title should give a fair overview of the problem but I'm running a dynamic named range for use in a combo box in a userform. When I run the form, the values appear as intended. When I call a module sub-routine via a command button, the values don't appear and I've no idea why.
标题应该对问题给出一个公平的概述，但我正在运行一个动态命名范围以在用户表单的组合框中使用。当我运行表单时，值按预期显示。当我通过命令按钮调用模块子例程时，值不会出现，我不知道为什么。
I'll paste all code and highlight the offending snippet(s) below:
我将粘贴所有代码并在下面突出显示有问题的代码段：
Private Sub btnGetGAToken_Click()
'--------------------------------
'Obtain API Token from Google Analytics (GA), indicate to user that token has been obtained and populate Account combobox
'with a unique list of accounts, which will in turn populate the Profile combobox with the profiles associated with the chosen
'account
'--------------------------------
Dim txtEmailField As String
Dim txtPasswordField As String
'Values written to sheet for use in UDFToken and UDFGetGAAcctData array formulas
Range("FieldEmail").Value = Me.txtEmailField.Text
Range("FieldPassword").Value = Me.txtPasswordField.Text
Range("GAToken").Calculate
With Me.lblGATokenResponseField
.Caption = Range("GAToken").Value
.ForeColor = RGB(2, 80, 0)
End With
Call FindUniqueAccountNames
cboAccountNamesComboBox.RowSource = Sheet1.Range("ListUniqueAccountNames").Address
End Sub
Private Sub cboAccountNamesComboBox_Change()
'Value written to sheet for use in the 'ListProfileNames' dynamic, named range
Range("ChosenAccount").Value = Me.cboAccountNamesComboBox.Value
With Me.cboProfileNamesComboBox
.Value = ""
.RowSource = Sheets("CodeMetaData").Range("ListProfileNames").Address
End With
End Sub
The dynamic range was created using the name manager and is below:
动态范围是使用名称管理器创建的，如下所示：
Named Range: "ListUniqueAccountNames" =OFFSET(CodeMetaData!$J$5,0,0,COUNTA(CodeMetaData!$J$5:$J$5000))
命名范围："ListUniqueAccountNames" =OFFSET(CodeMetaData!$J$5,0,0,COUNTA(CodeMetaData!$J$5:$J$5000))
and for ease of reference, the code I'm using to run it is below:
为了便于参考，我用来运行它的代码如下：
cboAccountNamesComboBox.RowSource = Sheets("CodeMetaData").Range("ListUniqueAccountNames").Address
The sub-routine calling the userform is here:
调用用户窗体的子程序在这里：
Public Sub ShowReportSpecsForm()
Load frmReportSpecs
frmReportSpecs.Show
End Sub
Forgive me for posting so much of the code, but I'm not sure exactly what it is that's causing the problem - I'm still very much a rookie with forms.
请原谅我发布了这么多代码，但我不确定到底是什么导致了问题 - 我仍然是表单的新手。
Any help will be greatly appreciated. Thanks.
任何帮助将不胜感激。谢谢。
回答by Reafidy
If you are using the rowsource property and named ranges then I would suggest setting the rowsource property of the combobox's at design time. Then to debug where required use:
如果您使用 rowsource 属性和命名范围，那么我建议在设计时设置组合框的 rowsource 属性。然后在需要使用的地方进行调试：
Debug.Print Range("ListUniqueAccountNames").Address
This will return the named range address to the immediate window where you can check it is correct.
这会将命名范围地址返回到立即窗口，您可以在其中检查它是否正确。
回答by Paulo Buchsbaum
Remember that the property Addressfrom a named dynamic range returns a normal static address.
请记住，命名动态范围中的属性Address返回一个正常的静态地址。
For example, Range("ListUniqueAccountNames").Addresscan returns $J$5:$J$20.
例如，Range("ListUniqueAccountNames").Address可以返回$J$5:$J$20。
You do not need use a Excel address in RowSourceproperty. You can use a Excel name.
您不需要在RowSource属性中使用 Excel 地址。您可以使用 Excel 名称。
Besides, when you show a Userform it is necessary refresh the RowSourceproperty from a ComboBox or ListBox control in order to update its values. (Excel control does not watch if the range or data change)
此外，当您显示用户窗体时，有必要从 ComboBox 或 ListBox 控件刷新RowSource属性以更新其值。（Excel 控件不监视范围或数据是否发生变化）
That refresh can be made inside Activate event (it runs immediately before form Show and it is shown below) and any situation where data or range changes.
该刷新可以在 Activate 事件（它在表单 Show 之前立即运行，如下所示）以及任何数据或范围更改的情况下进行。
Private Sub UserForm_Activate()
Me.cboAccountNamesComboBox.RowSource = ""
Me.cboAccountNamesComboBox.RowSource = "ListUniqueAccountNames"
End Sub
