Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Dependent combobox

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dependent combobox

    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







  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,670
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Dependent combobox

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

  3. #3
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dependent combobox

    Quote Originally Posted by Akuini View Post
    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=1dT...BpW5mnR3Q2adQ6

    The names are not real but only fictional

    thank you very much

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,968
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Dependent combobox

    Cross-posted here: https://www.excelforum.com/excel-gen...-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-a...rum-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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dependent combobox

    Quote Originally Posted by Joe4 View Post
    Cross-posted here: https://www.excelforum.com/excel-gen...-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-a...rum-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

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,670
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Dependent combobox

    Ok, try this:

    Code:
    Private RGX As Range
    Private Sub ComboBox3_GotFocus()
    With Sheets("odberatel")
    ComboBox3.List = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)).Value
    End With
    End Sub
    Private Sub ComboBox3_Change()
    Dim r As Range
    Dim n As Long, rc As Long, i As Long
    ComboBox4.Clear
    If ComboBox3.Value <> vbNullString Then
            With Sheets("odberatel")
            Set r = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))
            
            If Not IsError(Application.Match(ComboBox3.Value, r, 0)) Then
            n = Application.Match(ComboBox3.Value, r, 0)
            rc = .Cells(n, Columns.Count).End(xlToLeft).Column
            Set RGX = .Range(.Cells(n, "H"), .Cells(n, rc))
                If rc > 9 Then
                    For i = 8 To rc Step 2
                    ComboBox4.AddItem .Cells(n, i)
                    Next
                    ComboBox4.Value = ComboBox4.List(0)
                Else
                Range("C24") = .Cells(n, "H")
                Range("C25") = .Cells(n, "I")
                End If
            End If
            
            End With
    End If
    End Sub
    
    
    Private Sub ComboBox4_Change()
    Dim q As Range
    
    If ComboBox4.Value <> "" Then
        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) = ""
    End If
    
    End Sub

    The workbook:
    https://www.dropbox.com/s/isbjrpzqii...%201.xlsm?dl=0

  7. #7
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dependent combobox

    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
        ComboBox4.Visible = True ' if there are more contact persons the ComboBox4 will be shown
        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) = ""
        ComboBox4.Visible = False ' if there is only one contact person the ComboBox4 will be hidden
    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
    Last edited by Imh0tep; Mar 16th, 2019 at 03:16 PM.

  8. #8
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,670
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Dependent combobox

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

    Code:
    Private Sub ComboBox3_Change()
    Dim r As Range
    Dim n As Long, rc As Long, i As Long
    ComboBox4.Clear
    If ComboBox3.Value <> vbNullString Then
            With Sheets("odberatel")
            Set r = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))
            
            If Not IsError(Application.Match(ComboBox3.Value, r, 0)) Then
            n = Application.Match(ComboBox3.Value, r, 0)
            rc = .Cells(n, Columns.Count).End(xlToLeft).Column
            Set RGX = .Range(.Cells(n, "H"), .Cells(n, rc))
                If rc > 9 Then
                    For i = 8 To rc Step 2
                    ComboBox4.AddItem .Cells(n, i).Value
                    Next
                    Range("C24").Resize(2, 1) = ""
                    ComboBox4.Visible = True ' if there are more contact persons the ComboBox4 will be shown
    
                Else
                Range("C24") = .Cells(n, "H")
                Range("C25") = .Cells(n, "I")
                ComboBox4.Visible = False ' if there is only one contact person the ComboBox4 will be hidden
    
    
                End If
            End If
            
            End With
    End If
    End Sub
    
    
    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: Exit For
            Next
        End If
    Else
        Range("C24").Resize(2, 1) = ""
    End If
    
    End Sub

  9. #9
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dependent combobox

    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

  10. #10
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,670
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Dependent combobox

    Quote Originally Posted by Imh0tep View Post
    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
                Worksheets("bestaetigung").Range("C25") = q.Offset(, 1).Value
                Exit For
            End If
            Next
        End If
    Else
        Range("C24").Resize(2, 1) = ""
    End If
    
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •