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

[OneCurtis]

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.

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

I'll paste all the code I have below, just in case my assumption about the cause is wrong:

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

Forgive me for posting so much of the code, but I'm still very much a rookie with forms and I'm not sure exactly what it is that's causing the problem.

Any help will be greatly appreciated. Thanks.

 

[Norie]

Well here's the link to the file.

http://www.box.net/shared/vgtntyxy8lom7z2e9uqu

The code might not be too clear - I didn't get really get a chance to comment or tidy it up.

Also you'll probably notice a bit of screen flicker.

That's because the code clears the results of the filters as it goes.

eg it puts the unique list for the Account Names in a range, like the original code, but once the combobox has been sorted it clears the range

The reason I did this was because the advanced filter(s) seemed to fail when the destination ranges already had data.

I've experienced that before and the easiest way to fix the problem was to simply clear the range, or even the worksheet being used for the filter results.

Anyway, give it a go and see what you think.

I'm sure there'll be some problems with it.

 

[OneCurtis]

Norie this looks great. I'm at work and stupidly left the file at home, so I'll test it later this evening and let you know how it goes.

Thanks very much for taking the time to do that for me, I sincerely appreciate it.

 

[Norie]

No problem, if it doesn't work post back - I've got a feeling I might have done something wrong and/or overcomplicate things.

 

[OneCurtis]

Perhaps if you use the External argument of the .Address property

cboAccountNamesComboBox.RowSource = ThisWorkbook.Names("ListUniqueAccountNames").RefersToRange.Address(,,,True)

Mike, sorry I didn't see this before. it actually works a treat - the list/value combo works but falls down when there's only one profile.

Norie - I'm going to try it the codeless way when I'm optimising the app next week. I've got a really short deadline - shorter still after losing a day yesterday - and I just need a demonstrable version to begin with.

Thank you both for your help. Better than stackoverflow any day!

 

[Norie]

You could try not using he Address property.

With a named range you can simply use the name.

cboAccountNamesComboBox.RowSource = "ListUniqueAccountNames"

Don't know if the range being dynamic makes a difference mind you.