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]

Where's the code for the sub FindUniqueAccountNames?

 

[OneCurtis]

Sorry, I'd forgotten about this one. Here's the code:

Sub FindUniqueAccountNames()

'generates a list of unique account names from the returned GA data to be displayed in a dropdown list on the 'CodeMetaData' sheet
    Dim rngSourceCell As Range
    Dim rngTargetCell As Range

    Set rngSourceCell = Sheets("CodeMetaData").Range("F4:F752")
    Set rngTargetCell = Sheets("CodeMetaData").Range("J4")
    
    If Sheets("CodeMetaData").Range("A3").Value <> "Email:" Then
        Exit Sub
    Else
        rngSourceCell.AdvancedFilter xlFilterCopy, , rngTargetCell, True
    End If

End Sub

 

[Norie]

Do you actually need the dynamic named range?

I actually think it might be the problem, perhaps it isn't getting updated for some reason.

Couldn't you populate the combobox without it?

You could still use a named range but instead if creating it using worksheet formulas you can do it in the code.

Something like this perhaps?

With Worksheets("CodeMetaData")

   .Range("F" & Rows.Count) 
   .Range("F5:F" & LastRow).Name ="ListProfileNames"

End With

I would also suggest you don't use the RowSource property.

You can populate the combobox using it's List property.

cboProfileNamesComboBox.List = Range("ListProfileNames").Value

If you do want to use RowSource just use the name, not the address.

The address doesn't actually return the worksheet, it only returns the range.

So you could find yourself populating the combobox from the right range but on the wrong worksheet.

 

[OneCurtis]

Norie, you're an absolute gent. I've initially gone with the List/Value combination and it works perfectly.

There's no real need for the worksheet formula for that particular range, but I have another one that's dependent on this one for the next combobox and I wasn't sure how to translate its function into vba - I have a deadline and I just need it to work for now

The dependent dynamic range is below (Name: "ListProfileNames"):

=OFFSET(CodeMetaData!$F$4,MATCH(CodeMetaData!$B$17,CodeMetaData!$F$4:$F$5000,0)-1,1,COUNTIF(CodeMetaData!$F$4:$F$5000,CodeMetaData!$B$17))

It basically creates a list of profiles that have the specified account name to the left of them. I've currently got the code set up to write the value of cboAccountNamesComboBox to cell B17 on change for use in the formula. If you have time, any suggestions for how I could make that cleaner would be most appreciated, but if not thanks for your help so far, you've been a tremendous help.

 

[Norie]

Oops, I made a mistake.

Looks like I've misunderstood what named ranges you had and where they were being used.

You can still use what I suggested though, but for ListUniqueNames.

As for the ListProfileNames I think it can be done in code, if I understand it correctly that is.

Can you post some more information on the actual set up?

eg which column each set of data is in

I think I know (column F profiles, column G names)but I'm not 100% sure.

Might even be wildly wrong.

 

[mikerickson]

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

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

 

[OneCurtis]

Oops, I made a mistake.

Looks like I've misunderstood what named ranges you had and where they were being used.

You can still use what I suggested though, but for ListUniqueNames.

As for the ListProfileNames I think it can be done in code, if I understand it correctly that is.

Can you post some more information on the actual set up?

eg which column each set of data is in

I think I know (column F profiles, column G names)but I'm not 100% sure.

Might even be wildly wrong.

No worries, it's hard to keep track of these things without seeing them - although you managed it perfectly. Here's a screenshot of dummy data: Screenshot (it's a big image so didn't want to post it directly).

 

[Norie]

I came up with something that doesn't use named ranges at all.

Not sure if that would be suitable though, especially if you rely on the named ranges elsewhere in the code or in the workbook.

I can post the code and/or a link to a file I uploaded.

 

[OneCurtis]

I only use those ranges for that purpose, nowhere else in the app at all. I'd be really grateful to see how you did it, because the code will undoubtedly be easier to maintain than the worksheet formula.

Thanks for all your help on this.