Dependent combobox

Imh0tep · Mar 14, 2019

Hi all,

I don't speak english very well but I will try to describe my problem.

I have a combobox with names of customers on "Sheet1". The name of corresponding contact person and his email will show in C24 and C25 after choosing a customer from combobox.

I have list of customer on "Sheet2". Every customer can have one, two or three contact persons. Would it be possible to do following using VBA?

If I choose for example second customer (customer "Best a.s.") with three contact persons I want to display second (new) combobox with three contact persons which belong to this customer (in my example "Tomas Miculka"; "Petr Nadvornik" and "Josef Pecha"). If I choose the first customer (FBM s.r.o.) I want to display only two contact persons ("Josef Novy" and "Igor Hnizdo") in the new combobox. Afterthat I choose one contact person from the second combobox and this contact person will show in C24 and his E-mail in C25.

If the customer has only one contact person I don't want to display any other combobox but directly write the contact person and e-mail to C24 and C25

The number of the contact persons can change - I can delete or add some contact person. So there is no fix number of contact persons by every customer.

Could you please help me with this issue?

Thank you very much

Miroslav

Akuini · Mar 14, 2019

Could you upload your workbook (without sensitive data) somewhere (maybe to dropbox.com or google drive)? Then put the link here.

Imh0tep · Mar 15, 2019

Akuini said:
Could you upload your workbook (without sensitive data) somewhere (maybe to dropbox.com or google drive)? Then put the link here.

here is it

https://drive.google.com/open?id=1dTQS_OAWlVL_zMvWmMBpW5mnR3Q2adQ6

The names are not real but only fictional

thank you very much

Joe4 · Mar 15, 2019

Cross-posted here: https://www.excelforum.com/excel-general/1268515-dependent-combobox.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

This way, other members can see what has already been done in regard to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: http://www.excelguru.ca/content.php?184).

Imh0tep · Mar 15, 2019

Joe4 said:
Cross-posted here: https://www.excelforum.com/excel-general/1268515-dependent-combobox.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

This way, other members can see what has already been done in regard to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: http://www.excelguru.ca/content.php?184).

Hello,

I have no idea who the user tutu10 is. I'm from Czech republic. tutu10 had to copy my inquiry and created the same thread.

I just need help with my issue.

thank you all

Akuini · Mar 15, 2019

Ok, try this:

Code:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] RGX [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox3_GotFocus()
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"odberatel"[/COLOR])
ComboBox3.List = .Range([COLOR=brown]"B2"[/COLOR], .Cells(.Rows.Count, [COLOR=brown]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp)).Value
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox3_Change()
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], rc [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
ComboBox4.Clear
[COLOR=Royalblue]If[/COLOR] ComboBox3.Value <> vbNullString [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"odberatel"[/COLOR])
        [COLOR=Royalblue]Set[/COLOR] r = .Range([COLOR=brown]"B1"[/COLOR], .Cells(.Rows.Count, [COLOR=brown]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
        
        [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] IsError(Application.Match(ComboBox3.Value, r, [COLOR=crimson]0[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
        n = Application.Match(ComboBox3.Value, r, [COLOR=crimson]0[/COLOR])
        rc = .Cells(n, Columns.Count).[COLOR=Royalblue]End[/COLOR](xlToLeft).Column
        [COLOR=Royalblue]Set[/COLOR] RGX = .Range(.Cells(n, [COLOR=brown]"H"[/COLOR]), .Cells(n, rc))
            [COLOR=Royalblue]If[/COLOR] rc > [COLOR=crimson]9[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]8[/COLOR] [COLOR=Royalblue]To[/COLOR] rc [COLOR=Royalblue]Step[/COLOR] [COLOR=crimson]2[/COLOR]
                ComboBox4.AddItem .Cells(n, i)
                [COLOR=Royalblue]Next[/COLOR]
                ComboBox4.Value = ComboBox4.List([COLOR=crimson]0[/COLOR])
            [COLOR=Royalblue]Else[/COLOR]
            Range([COLOR=brown]"C24"[/COLOR]) = .Cells(n, [COLOR=brown]"H"[/COLOR])
            Range([COLOR=brown]"C25"[/COLOR]) = .Cells(n, [COLOR=brown]"I"[/COLOR])
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]


[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox4_Change()
[COLOR=Royalblue]Dim[/COLOR] q [COLOR=Royalblue]As[/COLOR] Range

[COLOR=Royalblue]If[/COLOR] ComboBox4.Value <> [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    Range([COLOR=brown]"C24"[/COLOR]) = ComboBox4.Value
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] q [COLOR=Royalblue]In[/COLOR] RGX
        [COLOR=Royalblue]If[/COLOR] q = ComboBox4.Value [COLOR=Royalblue]Then[/COLOR] Range([COLOR=brown]"C25"[/COLOR]).Value = q.Offset(, [COLOR=crimson]1[/COLOR]).Value: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
[COLOR=Royalblue]Else[/COLOR]
    Range([COLOR=brown]"C24"[/COLOR]).Resize([COLOR=crimson]2[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

The workbook:
https://www.dropbox.com/s/isbjrpzqiiunx67/Faktura 1.xlsm?dl=0

Imh0tep · Mar 16, 2019

to Akuini: It's amazing. Thank you very much. I really appreciate your help.

I upgraded your code with show/hide ComboBox

Code:

Private Sub ComboBox4_Change()
Dim q As Range

If ComboBox4.Value <> "" Then
    [U][B]ComboBox4.Visible = True ' if there are more contact persons the ComboBox4 will be shown[/B][/U]
    Range("C24") = ComboBox4.Value
        For Each q In RGX
        If q = ComboBox4.Value Then Range("C25").Value = q.Offset(, 1).Value: Exit For
        Next
Else
    Range("C24").Resize(2, 1) = ""
    [U][B]ComboBox4.Visible = False ' if there is only one contact person the ComboBox4 will be hidden[/B][/U]
End If

End Sub

Could I ask to you to upgrade your solution about following point please?

If I select a customer with multiple contact persons Range "C24" and "C25" will be empty till I select corresponding contact person from new displayed ComboBox4

Thanks in advance

Akuini · Mar 16, 2019

Ok, it’s better to set the combobox4 visible property in “Sub ComboBox3_Change”.
Like this:

Code:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox3_Change()
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], rc [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
ComboBox4.Clear
[COLOR=Royalblue]If[/COLOR] ComboBox3.Value <> vbNullString [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"odberatel"[/COLOR])
        [COLOR=Royalblue]Set[/COLOR] r = .Range([COLOR=brown]"B1"[/COLOR], .Cells(.Rows.Count, [COLOR=brown]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
        
        [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] IsError(Application.Match(ComboBox3.Value, r, [COLOR=crimson]0[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
        n = Application.Match(ComboBox3.Value, r, [COLOR=crimson]0[/COLOR])
        rc = .Cells(n, Columns.Count).[COLOR=Royalblue]End[/COLOR](xlToLeft).Column
        [COLOR=Royalblue]Set[/COLOR] RGX = .Range(.Cells(n, [COLOR=brown]"H"[/COLOR]), .Cells(n, rc))
            [COLOR=Royalblue]If[/COLOR] rc > [COLOR=crimson]9[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]8[/COLOR] [COLOR=Royalblue]To[/COLOR] rc [COLOR=Royalblue]Step[/COLOR] [COLOR=crimson]2[/COLOR]
                ComboBox4.AddItem .Cells(n, i).Value
                [COLOR=Royalblue]Next[/COLOR]
                Range([COLOR=brown]"C24"[/COLOR]).Resize([COLOR=crimson]2[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR]
                ComboBox4.Visible = [COLOR=Royalblue]True[/COLOR] [I][COLOR=seagreen]' if there are more contact persons the ComboBox4 will be shown[/COLOR][/I]

            [COLOR=Royalblue]Else[/COLOR]
            Range([COLOR=brown]"C24"[/COLOR]) = .Cells(n, [COLOR=brown]"H"[/COLOR])
            Range([COLOR=brown]"C25"[/COLOR]) = .Cells(n, [COLOR=brown]"I"[/COLOR])
            ComboBox4.Visible = [COLOR=Royalblue]False[/COLOR] [I][COLOR=seagreen]' if there is only one contact person the ComboBox4 will be hidden[/COLOR][/I]


            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]


[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox4_Change()
[COLOR=Royalblue]Dim[/COLOR] q [COLOR=Royalblue]As[/COLOR] Range

[COLOR=Royalblue]If[/COLOR] ComboBox4.Value <> [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    Range([COLOR=brown]"C24"[/COLOR]) = ComboBox4.Value
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] RGX [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] q [COLOR=Royalblue]In[/COLOR] RGX
        [COLOR=Royalblue]If[/COLOR] q = ComboBox4.Value [COLOR=Royalblue]Then[/COLOR] Range([COLOR=brown]"C25"[/COLOR]).Value = q.Offset(, [COLOR=crimson]1[/COLOR]).Value: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Else[/COLOR]
    Range([COLOR=brown]"C24"[/COLOR]).Resize([COLOR=crimson]2[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Imh0tep · Mar 24, 2019

thanks a lot

I have one more question.

I need to show contact person and his e-mail address from the combobox in another worksheet also. With the name it works well but with the email I don't know why.

I tried to use AND but without success

Could you please help me?

thank you

Code:

Private Sub ComboBox4_Change()Dim q As Range


If ComboBox4.Value <> "" Then
    Range("C24") = ComboBox4.Value
    Worksheets("bestaetigung").Range("C24") = ComboBox4.Value ' works fine
    If Not RGX Is Nothing Then
        For Each q In RGX
        If q = ComboBox4.Value Then Range("C25").Value = q.Offset(, 1).Value: Exit For ' here I need to show the e-mail address simultaneously also in Worksheets("bestaetigung").Range("C25")
        Next
    End If
Else
    Range("C24").Resize(2, 1) = ""
End If
End Sub

Akuini · Mar 26, 2019

Imh0tep said:
I need to show contact person and his e-mail address from the combobox in another worksheet also. With the name it works well but with the email I don't know why.

Try this:

Code:

Private Sub ComboBox4_Change()
Dim q As Range

If ComboBox4.Value <> "" Then
    Range("C24") = ComboBox4.Value
    If Not RGX Is Nothing Then
        For Each q In RGX
        If q = ComboBox4.Value Then
            Range("C25").Value = q.Offset(, 1).Value
            [COLOR=#0000ff]Worksheets("bestaetigung").Range("C25") = q.Offset(, 1).Value[/COLOR]
            Exit For
        End If
        Next
    End If
Else
    Range("C24").Resize(2, 1) = ""
End If

End Sub

Dependent combobox

Imh0tep

New Member

Excel Facts

Akuini

Well-known Member

Imh0tep

New Member

Joe4

MrExcel MVP, Junior Admin

Imh0tep

New Member

Akuini

Well-known Member

Imh0tep

New Member

Akuini

Well-known Member

Imh0tep

New Member

Akuini

Well-known Member

Similar threads

Forum statistics

Share this page

Dependent combobox

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP, Junior Admin

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock