Doubts in the combobox

Question

I have two combobox, the first is the managers and the second I have the coordinators.

MANAGER - FABIO - ADRIANA - LEANDRO

COORDINATORS - TEREZA - GERALDO - GABRIELE - JOICE - VALDIR -ROSE - KATIA -LUCIA - PAULO - REBECA

When I select in the manager combo Fabio In the coordinator combo will have to show GABRIELE - LUCIA TEREZA

When I select in the combo manager LEANDRO In the coordinator combo will have to show GERALDO - REBECA - JOICE

When I select in the manager combo Adriana In the coordinator combo will have to show KATIA - PAULO - VALDIR

Sub PreencherComboboxCoord()
    Dim wsc    As Worksheet
    Dim wsr    As Worksheet
    Dim celula As Range
    Dim NGerente As String

    Set wsc = Sheets("Custos de 01 a 1310")
    Set wsr = Sheets("Resultado")

    wsc.Select
    wsc.Range("M2").Select 'Coordenador

     With Planilha3
        NGerente = .Cbo_Gerente.Value
       .Cbo_Coordenador.Clear
     End With

   Do

   With PlanCusto ' planilha onde vou extrair os dados


        On Error Resume Next
        Pesquisa = .Range("M2").Address & ":" & ActiveCell.Address
        contar = WorksheetFunction.CountIf(.Range(Pesquisa), ActiveCell.Text)

            With Planilha3 ' Resultado onde está as combox

                If contar <= 1 Then
                    If PlanCusto.Range(Pesquisa) = NGerente Then
                        .Cbo_Coordenador.AddItem ActiveCell.Text
                    End If
                End If

            End With

        ActiveCell.Offset(1, 0).Select
        If ActiveCell.Value = Empty Then
            wsr.Select
            Exit Sub
        End If

  End With

  Loop

In other words, every manager has their coordinators, and it’s this relationship that I’m not having.

I await grateful return

Answer 1

Try using the method. Additem

        With Cbo_Coordenador
            .AddItem
            .List(.ListCount - 1) = "nome do coordenador1"
            .AddItem
            .List(.ListCount - 1) = "nome do coordenador2"
        End With

This would be a very manual method, the recommended would be you create a relationship table between Manager and Coordinator.

Answer 2

You can do as follows, if you are in a Userform use the . Additem when Userform is started (as Francisco Salome showed), in this way:

Private Sub UserForm_Initialize()
    With ComboBox_Gerente
        .AddItem
        .List(.ListCount - 1) = "Fabio"
        .AddItem
        .List(.ListCount - 1) = "Adriana"
        .AddItem
        .List(.ListCount - 1) = "Leandro"
    End With
End Sub

Thus, when Userform is started, the Combobox_manager will receive the items Fabio, Adriana and Leandro. After that, add the following code in the Combobox_manager

Private Sub ComboBox_Gerente_Change()

    ' Criar uma variavel com o nome "opc" (opção) como String (tipo de variavel para caracteres)'
    Dim opc As String

    ' A variavel opc é igual á opção selecionada no ComboBox_Gerente'
    opc = Me.ComboBox_Gerente.Text

    ' Se a variavel opc for igual a Fabio então o '
    'ComboBox_Coordenadores será igual a "GABRIELE - LUCIA - TEREZA", e o mesmo para as outras opções'
    If opc = "Fabio" Then
        Me.ComboBox_Coordenadores.Text = "GABRIELE - LUCIA - TEREZA"
        ElseIf opc = "Leandro" Then
            Me.ComboBox_Coordenadores.Text = "GERALDO - REBECA - JOICE"
            ElseIf opc = "Adriana" Then
                Me.ComboBox_Coordenadores.Text = "KATIA - PAULO - VALDIR"
    End If

End Sub

So, when you select one of the options in the Combobox_managers, the Combobox_coordinators will receive the options referring to that name.

I hope I’ve helped!