Third defined combobox (activeX)

[patzkata]

Hello everybody.

This is my first post.
i found this forum very useful, and now i have problem which i want to solve, and i didnt find the solution into the forum.

So.. i have some kind of a form , which collecting data for me based on the options i choose from a dropdown menu
I what to use comboboxes, because of the autocomplete function which they have, which is very useful when using long defined list as I do.

I need to implement somehow this formula into the combobox ( " =INDIRECT(SUBSTITUTE(D12&D14," "," ")) ")

What i have is a Combobox1 which is with pre-defined listfill which directs to a "namelist" named Sport , which included sports (Football, Tennis, Basektball...etc)

and Im using this code in combobox1 to tell Combobox2 to look for a specific SUBname list

Private Sub Combobox1_Change()

Me.ComboBox2.Clear
Dim ListCell As Range
For Each ListCell In Range(Me.ComboBox1.List(Me.ComboBox1.ListIndex))
Me.ComboBox2.AddItem (ListCell.Value)
Next ListCell
End Sub

Combobox2 is my CLASS option , where if in Combobox1 I choose "Football", in combobox2 now i have (England, Germany..etc) which are all pre-defined name ranges


What Im trying to do i to tell my comboxbox3 to look for the combination for Combo1&2 and to give me the next optional list ...

Example (in COmbo1 if I have Football, then i Combo2 i choose "England" , Then in combobox3 I need to give me list with the England leagues to choose)

I hope i didnt sound very complicated.

I will appreciate every help
Thanks

[torppo]

What about if you use Select Case?

Dim sport as string =Combobox1.value
Dim cntry as string=Combobox2.value

Select Case True
Case (sport = Football and cntry =England)
Combobox3.Additem=Team1
Combobox3.additem=Team2
etc...

[patzkata]

im not sure it that will work for my case. As i mentioned, i have long list

i have already pre-defined namerages
which for the case are "Sport" (Football, Tennis...)
another ranges named as the sport name "Football" , "Tennis" ... the Football range has let say England, Germany ... et
and I have combined nameranges such as "FootballEngland" - which shows the leagues from England

this name ranges working for a data validation list easily, but its really uncomfortable to choose from longest lists

thats why i think that your solution will not work , because i have to do a really long code, if I understood you right
Thanks

[pipoliveira]

Hi patzkata,

I hope this helps.

Please check the file attached and see how it works. Click the button and you will have different options based on what you choose.

Basically, I have created range names for few cars and motorcycle mades and models as an example.

Please let me know if that's what you're looking for.

Best regards,
Filipe

[patzkata]

Hi,

that is exactly what i need to
but im getting an error when i tried to implement your code into my case
am attaching you my example
would you be so kind as to take a look

test1.xlsm

Hi patzkata,

I hope this helps.

Please check the file attached and see how it works. Click the button and you will have different options based on what you choose.

Basically, I have created range names for few cars and motorcycle mades and models as an example.

Please let me know if that's what you're looking for.

Best regards,
Filipe

[patzkata]

Im getting error on the line "With Range(ComboBox2.Value)"

where im getting wrong? Any idea
Thanks a lot

Please Login or Register  to view this content.

[patzkata]

Im getting error on the line "With Range(ComboBox2.Value)"

where im getting wrong? Any idea
Thanks a lot

Please Login or Register  to view this content.

[pipoliveira]

Hi patzkata,

It might be a easier way to do it, I just remember this one that I use for only 2 ComboBoxes.

I have changed the code for ComboBox1. Now it's working for CB2 but the problem is the CB3. You did not had defined a Range Name for the 3rd criteria.

e.g. if you choose "Football" on the CB1, the CB2 will show you the name range you set as football. If you choose the class "ENG" it will give you an error because you need to create a named range for the "ENG" criteria that will show on the ComboBox3.

Please check your file attached and let me know if I made myself clear on the above.

Best regards,
Filipe

[patzkata]

Hi Filipe,

first thanks for the Fast reply

i understand there im wrong
its little bit different from the normal data validation drop-downs, where if I want to use a substitute, the defined name should combine the both previous names like "FootballENG"
here we are using only ENG as you mentioned.
but now my question is, how can I separate this, because i have ENG in Football, but I have ENG in Hockey as well as an example
Thanks

[pipoliveira]

Hey patzkata,

As I said, it might be another solution more simple than mine, so I advise you to keep this thread open to see if someone can reply with a much easier solution.

For now, all I can advise you to do (this in the case you want to use my idea), is to have the named ranges as: ENG in Football as F_ENG; ENG in Hockey as H_ENG and so on.

I know it won't look nice to have it like that but it's the only idea I have at the moment. I am still a beginner in VBA. Ah! If you create the name ranges as above, do not forget to do the same on the Football list or else Excel will not recognize it.

Hope it helps.

Best regards,
Filipe

[patzkata]

Hi Filipe,

thanks once again. I was thinking for the same thing.
I guess i have to re-name most of the stuff , but at least i will have my combos working
Regards

[pipoliveira]

Hey patzkata,

I got it!

You can have the named range as FootballENG for example. You do not need to create new ones.

Before you had:

Please Login or Register  to view this content.

Now, just change it to:

Please Login or Register  to view this content.

The outlined line will join the value from CB1 and CB2. e.g. if you choose the sport "Football" and the class "ENG", the third ComboBox will look for a name range as "FootballENG".

I've tried and it's working. you just need to create the named range with all possible criterias.

Let me know if you need more assistance on this matter.

Kind regards,
Filipe

[patzkata]

Hi again,

i just changed the code , but i got some error again
have to find what causing it
Thanks

edit:
never mind what i just said...
it was the defined name, its really working.
Thanks a lot mate.
You have a beer from me, or whatever you drinking

Regards

[pipoliveira]

Maybe you didn't defined the name ranges yet.

I saw your file and you did not created the FootballENG named range for example. You have the tables there, just need to named those ranges.

In case of errors, please let me know so I can check. (Maybe not today, i'm leaving for now).

Cheers.

[pipoliveira]

Hi patzkata,

I hope you're doing well.

Just a follow up message as the thread is still not solved.

Did you managed to solve your query? Did you found any issues?

Best regards,
Filipe

[patzkata]

hi pipoliveira

i completely forgot to mark the threat "solved"

you helped me a lot, and everithing is working very well more than a month

thank you