Dependant Activex ComboBox duplicating the items

**VBAPAZ** · 05-18-2017, 03:29 PM

Hello All

I have 3 activex combobox in a spreadsheet. One for Region, another for Country and a last one for City. The region depends on the selection in the list country, and the city depends on the selection in region.

I have 3 dependent named ranges that are populated by a macro in a different spreadsheet. Example:

DESCTY =OFFSET(DesReg_Start,(MATCH(FreightCalculator!$A$14,DesReg_Column,0)-1),1,COUNTIF(DesReg_Column,FreightCalculator!$A$14),1)

Then another macro populates the combo box like this:

ComboBox9.ListFillRange = "DESCTY"

It works, but for some reason, the list of restricted Cities is showing more than one time. For example, if the Region selected is US East Coast, then it should show
Baltimore
Boston
Buffalo
.
.

But instead, it is showing

Baltimore
Boston
Buffalo
.
.
Baltimore
Boston
Buffalo
.
.
Baltimore
Boston
Buffalo
.
.

The dependent named range works fine. It shows correctly, but for some reason when it is feed to the combo box it duplicates it several times

What could be causing this issue?

**AlphaFrog** · 05-19-2017, 03:06 AM

DESCTY =OFFSET(DesReg_Start,(MATCH(FreightCalculator!$A$14,DesReg_Column,0)-1),1,COUNTIF(DesReg_Column,FreightCalculator!$A$14),1)

The above code is not the syntax to define a named range. It sets a variable called DESCY, not a named range. Though they may share the same name, a variable and a named range are two separate things. I suspect your code is setting a variable, but the combobox is using a previously defined named range.

Something like this would be the syntax to define a Named Range.
It presumes that DesReg_Start and DesReg_Column are range variables within the code.
Note: I'm not sure if this formula is exactly right based on the limited information provided.

    ActiveWorkbook.Names.Add Name:="DESCTY", RefersToR1C1:= _
        "=OFFSET(" & DesReg_Start.Address(ReferenceStyle:=xlR1C1) & ",(MATCH(FreightCalculator!R14C1," & _
        DesReg_Column.Address(ReferenceStyle:=xlR1C1) & ",0)-1),1,COUNTIF(" & DesReg_Column.Address(ReferenceStyle:=xlR1C1) & _
        ",FreightCalculator!R14C1),1)"

**VBAPAZ** · 05-22-2017, 10:44 AM

Hello

The line
DESCTY =OFFSET(DesReg_Start,(MATCH(FreightCalculator!$A$14,DesReg_Column,0)-1),1,COUNTIF(DesReg_Column,FreightCalculator!$A$14),1)

is not VBA. It is the way I defined the named range in Excel so it is a dependent drop down list

I am using VBA to assign the named range to the activex control

**AlphaFrog** · 05-22-2017, 10:52 AM

DESCTY =OFFSET(DesReg_Start,(MATCH(FreightCalculator!$A$14,DesReg_Column,0)-1),1,COUNTIF(DesReg_Column,FreightCalculator!$A$14),1)

This is a guess: you use other named ranges to define DESCTY. Perhaps you use DESCTY to define the other named ranges and that creates a circular reference?

**VBAPAZ** · 05-22-2017, 11:16 AM

Hello
I don't think the problem is the named range. When I select a country from the drop down "Country", and I go to Name Manage in Excel and select DESCTY it highlights the correct range in Excel...

I am really at lost here..

**AlphaFrog** · 05-22-2017, 11:22 AM

I don't knw either.

Maybe try clearing the combobox first.

ComboBox9.Clear
ComboBox9.ListFillRange = ""
ComboBox9.ListFillRange = "DESCTY"

Dependant Activex ComboBox duplicating the items

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Dependant Activex ComboBox duplicating the items

Re: Dependant Activex ComboBox duplicating the items

Re: Dependant Activex ComboBox duplicating the items

Re: Dependant Activex ComboBox duplicating the items

Re: Dependant Activex ComboBox duplicating the items

Re: Dependant Activex ComboBox duplicating the items

Thread Information

Users Browsing this Thread

Similar Threads

ActiveX ComboBox Control: add items without using ListFillRange Property

Worksheet ActiveX Combobox: Combobox displaying all fields instead of only the first field

[SOLVED] Dependendent ActiveX Combobox Limited To 8 Items In List

Multiple ActiveX ComboBox's w/several items - Is it possible to shorten?

ActiveX combobox datalist dependant on previous combobox selections

[SOLVED] fill listbox (ActiveX) based combobox (ActiveX)

[SOLVED] Make combobox list options dependant on selection in another combobox within a userform

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions