Excel VBA: Dynamic range for ComboBox.Rowsource values not displayed when userForm called from commandbutton

Question

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))

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.

Answer 1

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:

Debug.Print Range("ListUniqueAccountNames").Address

This will return the named range address to the immediate window where you can check it is correct.

Answer 2

Remember that the property Address from a named dynamic range returns a normal static address.

For example, Range("ListUniqueAccountNames").Address can returns $J$5:$J$20.

You do not need use a Excel address in RowSource property. You can use a Excel name.

Besides, when you show a Userform it is necessary refresh the RowSource property from a ComboBox or ListBox control in order to update its values. (Excel control does not watch if the range or data change)

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.

Private Sub UserForm_Activate()
  Me.cboAccountNamesComboBox.RowSource = ""
  Me.cboAccountNamesComboBox.RowSource = "ListUniqueAccountNames"
End Sub