Dynamic Named Range not working with Userform ComboBox

[Lil Stinker]

I'm having some trouble getting the dynamic properties of a named range to transfer to a ComboBox on a user form. I have a macro using an advanced filter that populates the sorted results based on the OptionButton selected on the form. Not having any trouble here, everything filters and sorts as it should. I'm using the following formula inside a named range to keep the results of the advanced filter dynamic and exclude any blank rows:

OFFSET('12-13_Client_Db'!$AQ$1,1,,COUNTA('12-13_Client_Db'!$AQ$1:$AQ$99999)-1,1)

Haven't had any issues using this formula in the past for dynamic drop down lists with on sheet Data Validation but with this user form ComboBox the results are less than stellar. My OptionButtons are All, Active & Inactive. The ComboBox should display the list of project names based on those OptionButtons. I get varying results in the ComboBox depending on the order the OptionButtons are selected. For example, if there are 20 names total under All and only 10 are Active and 10 Inactive, when I select All first, I see all 20 and then select Active or Inactive, I'll get 10 results with 10 blank rows. Vice versa, select Active first then select All, I only see 10 results of the full 20, no empty rows.

I have the RowSource for the ComboBox set to the dynamic Named Range.

Each OptionButton runs the macro (see below) that runs the advanced filter and sort on the Client_Db worksheet.

What am I missing? Why is the ComboBox displaying empty rows when it shouldn't and incomplete results when the resulting list is longer than the previous list selection?

Sub sortPROJECTS()

    With clientDB
        lastROW = .Range("A99999").End(xlUp).Row 'last row
        If lastROW < 2 Then Exit Sub 'no results, exit out
        '''sort by project type
        If AddClientForm.activeOPT.Value = True Then
            .Range("AC2").Value = "Active"
        ElseIf AddClientForm.inactiveOPT.Value = True Then
            .Range("AC2").Value = "Inactive"
        ElseIf AddClientForm.viewallOPT.Value = True Then
            .Range("AC2").Value = "<>"
        End If
        '''run advanced filter
        .Range("A1:Y" & lastROW).AdvancedFilter xlFilterCopy, .Range("AB1:AM2"), .Range("AQ1:BC1"), True
        lastRESROW = .Range("BC99999").End(xlUp).Row 'last result row
        If lastRESROW < 2 Then Exit Sub
        If lastRESROW < 3 Then GoTo SkipSort
        With .Sort
            .SortFields.Clear
            .SortFields.Add clientDB.Range("AQ2"), xlSortOnValues, xlAscending, DataOption:=xlSortNormal  'sort
            .SetRange clientDB.Range("AQ2:BC" & lastRESROW) 'set sort range
            .Apply
        End With
        .Calculate
SkipSort:
    End With

End Sub

Private Sub activeOPT_Click()
    sortPROJECTS

End Sub
Private Sub inactiveOPT_Click()
    sortPROJECTS
    
End Sub
Private Sub viewallOPT_Click()
    sortPROJECTS
    
End Sub

 

[Lil Stinker]

Can anybody tell me if it's possible to populate a UserForm ComboBox with a dynamic list when selecting

 

[Lil Stinker]

Can anybody tell me if it's possible to populate a UserForm ComboBox with different list results by selecting different OptionButtons to sort the list?

As I mentioned in my earlier post, I have three OptionButtons: Active, Inactive, & All. I have a ComboBox set to a dynamic Named Range using RowSource through Properties. And I have a worksheet Sub filtering and sorting the list for the Named Range based on those three criteria types. When selecting the appropriate OptionButton, I would like the ComboBox to display the updated list in the Named Range however, depending on the order in which the OptionButtons are selected, I either end up with fewer results than I should or blank rows following the listed items.

I've tried clearing RowSource before running the sort macro but that just clears the ComboBox entirely and I can't seem to get the code correct for setting RowSource again (even though it is still set in Properties).

I would appreciate any help you can offer. Even if it's to say it's not possible.

Private Sub inactiveOPT_Click()
    Me.field2.Value = ""
    sortPROJECTS
    Me.field2.RowSource = PROJECTS2023
    
End Sub

 

[NoSparks]

Any chance of you sharing a file so we can actually see what/why things are happening and can test possible solutions

 

[NoSparks]

What happens if instead of

I have the RowSource for the ComboBox set to the dynamic Named Range.

you use ComboBox.List = the dynamic Named Range.Value

 

[Lil Stinker]

What happens if instead of

you use ComboBox.List = the dynamic Named Range.Value

Thank you for responding! I was in the middle of setting up a Workbook to make available when you posted the second time. That did the trick! I had tried using .List as well but I kept receiving a "Run-Time 70 error: Permission Denied" so I abandoned it. However, when I tried it on the test Workbook, it worked. That's when I realized I had not deleted the Named Range from RowSource in Properties initially before running it again.

Now that RowSource is cleared out, .List works as it should. I knew it had to be something simple but when I get VBlocked, I really have a hard time seeing my way through it sometimes.

Appreciate the help! Thank you so much!

 

[NoSparks]

You're welcome, glad I could assist.