Populate Activex Combo box list from another sheet list

[sanket_sk]

Hi All,

I am working on Activex Combobox which is located in "Dashboard" sheet wherein I want to display dynamic range from another sheet called "Filters"

I have created the dynamic range in "Filters" A Column using the Filter function.

Also, I have VBA (pasted below) but this VBA is referencing in "Dashboard" sheet whereas i want it from Filter Sheet, is there any trick which we can use to populate data.

Dashboard Latest .xlsm
	A
1	Region
2	East
3	North
4	South
5	West
Filters

Cell Formulas
Range	Formula
A2:A5	A2	=SORT(UNIQUE(FILTER(ASC_List[Region],ASC_List[Region]=ASC_List[Region])),)
Dynamic array formulas.

Private Sub Region_L_Change()

Dim Rng As Range

Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Region_L.ListFillRange = Rng.Address
Region_L.ListIndex = 0

End Sub

 

[GWteB]

How about ...

Private Sub Region_L_Change()

    Dim Ws  As Worksheet
    Dim Rng As Range

    Set Ws = ThisWorkbook.Sheets("Filters")
    With Ws
        Set Rng = .Range(.Range("A2"), .Range("A" & .Rows.Count).End(xlUp))
    End With
    
    Region_L.ListFillRange = Rng.Address
    Region_L.ListIndex = 0

End Sub

 

[Fluff]

How about

Private Sub Region_L_Change()

Dim Rng As String

Rng = Sheets("Filters").Range("A2#").Address(, , , 1)
Region_L.ListFillRange = Rng
Region_L.ListIndex = 0

End Sub

 

[sanket_sk]

How about ...

Private Sub Region_L_Change()

    Dim Ws  As Worksheet
    Dim Rng As Range

    Set Ws = ThisWorkbook.Sheets("Filters")
    With Ws
        Set Rng = .Range(.Range("A2"), .Range("A" & .Rows.Count).End(xlUp))
    End With
   
    Region_L.ListFillRange = Rng.Address
    Region_L.ListIndex = 0

End Sub

Hi GWteB,

I tried this code, but the Combobox is not showing a list then I go back to code and run manually where system is trowing below error, please suggest..

Thanks,
Sanket

 

[GWteB]

Rng.Address evaluates to something like $A$1:$A$3 but you need a fully specified name, like in

"'[Book.xlsm]filters'!$A$1:$A$3"

I suggest you better use Fluff's code since you have Excel 365. Fluff's code provides a fully specified name.

 

[sanket_sk]

How about

Private Sub Region_L_Change()

Dim Rng As String

Rng = Sheets("Filters").Range("A2#").Address(, , , 1)
Region_L.ListFillRange = Rng
Region_L.ListIndex = 0

End Sub

Hi Fluff,

Thanks for the code, it is working fine, i can see desired list in combobox however, i am unable to select different regions, seems the code is getting reset every time i click/select the region.

Is there any trick by which we can control the resetting of the code?

Just to inform you, i am creating dependent combobox of Branch, City and so on using same code hence looking for control of the behaviour.

Sanket

 

[Fluff]

I'm afraid I don't understand what you mean when you say it's resetting.

 

[sanket_sk]

I'm afraid I don't understand what you mean when you say it's resetting.

Ok, SO Combobox is showing a list, on top of the list is East region

When click on North, West or South - combo boxt is again showing East only. (Unable to selet any value other that east)

Just to cross check combobox i removed VBA code and given hard coded range (Same range which VBA is returing), hard coded range is working fine i am able to select required region, i dont know what went wrong when i use VBA in combobox

Sanket

 

[Fluff]

Ok, remove this line

Region_L.ListIndex = 0

 

[sanket_sk]

Ok, remove this line

Region_L.ListIndex = 0

Hi Fluff,

Now working absolutely as per requirment, Thanks a lot for your valuable support

Thanks & Regards,

Sanket