none
filtrar entrada em combobox e vlookup/hlookup em textbox RRS feed

  • Pergunta

  • Bom dia Pessoal...

    Eu novamente... possuo uma planilha("Dados") na seguinte ordem:

    coluna A:Código

    colunaB:Descrição

    colunaC:Densidade

    colunaD:Linha

    colunaE:Fabricante

    colunaF:Custo

    ColunaG:Embalagem

     

    criei um form para cadastrar as formulas de cor, onde num form anterior o usuário seleciona a linha do produto, atribuindo a variavel "LinhaProd" o valor string da linha.

    No form de cadastro da fórmula tenho 12 combobox 12 textbox e +12 textbox dispostos em 3 colunas e 12 linhas... além dos botões. Gostaria de carregar os comboboxs apenas com os itens na planilha dados cujo valor da "Linha"(colunaD) fossem iguais ao que o usuário selecionou no form anterior.Sei carregar os comboboxs com todas as referências da coluna A, mas não sei como fazer o filtro pela variavel...

    depois disso gostaria que assim que selecionado o item na combox a textbox ao lado(a do meio) puxasse o valor "Descrição"(colunaB) para informação ao usuário...  pensei em usar Vlookup ou Hlookup, mas não me entendi bem com a função...preciso de uma ajudinha nisso...tipo em qual comando do combobox insiro o código(para assim que selecionar o item aparecer no textbox).. e como a vlookup/hlookup  na textbox da direita o usuário digitaria a quantidade desse item que vai na fórmula(ai tranquilo)..

    Desde Já agreço a todos e um ótimo domingo...

    Abraços;

    IJ

     

    P.S.: uso excel 2003 e estou fazendo estas operações nos forms do vba... Utilizo as planilhas do excel apenas para consultar dados, registrar as formulas e imprimir as etiquetas... Abraços

    domingo, 22 de agosto de 2010 14:57

Respostas

  • A sua rotina simplificado, ficaria então:

    Private Sub Preenche()
    
      Dim k As Long
      Dim n As Long
      Dim nLast As Long
    
      With Sheets("Dados")
        nLast = .Cells(.Rows.Count, "A").End(xlUp).Row 'Ler última célula preenchida da coluna A e atribuir a nLast.
        For n = 1 To nLast
          For k = 1 To 12
            If n = 1 Then 'Se é a primeira vez que os ComboBox serã povoados, limpá-los.
              Controls("ComboBox" & k).Clear
            End If
            Controls("ComboBox" & k).AddItem .Cells(n, "A")
          Next k
        Next n
      End With
    End Sub

    Note que alterei seu método de buscar informações da planilha. Uma prática não recomendada em VBA é selecionar uma planilha e ler os dados. É possível ler os dados diretamente de uma planilha sem que elas estejam selecionadas. Exemplo: Você pode povoar um ComboBox com dados da Plan1 mesmo se a Plan2 é a planilha selecionada.

    • Marcado como Resposta Ilário Júnior segunda-feira, 23 de agosto de 2010 22:50
    domingo, 22 de agosto de 2010 21:15
    Moderador
  • Olá Felipe...

    Gostei do teu exemplo... de mudar a opção caption do label.

    Pensei em diminuir ainda mais se possivel minhas linhas. Os comandos que funcionaram (de modo singelo) forma estes:

    Private Sub ComboBox2_Change()
      If ComboBox2.Value <> vbNullString Then
           cb = ComboBox2.Value
           FormulaCor_Form.Label2 = Application.WorksheetFunction.VLookup(cb, Sheets("Dados").[A2:F302], 2, 0)
           Cor02 = ComboBox2.Value
          
      Else
           FormulaCor_Form.Label2 = vbNullString
           Cor02 = vbNullString
      End If
    End Sub
    Private Sub ComboBox3_Change()
      If ComboBox3.Value <> vbNullString Then
           cb = ComboBox3.Value
           FormulaCor_Form.Label3 = Application.WorksheetFunction.VLookup(cb, Sheets("Dados").[A2:F302], 2, 0)
           Cor03 = ComboBox3.Value
           TesteDuplicata
      Else
           FormulaCor_Form.Label3 = vbNullString
           Cor03 = vbNullString
      End If
    End Sub

    ---------- e assim por diante ate o combobox 12. declarei cb como string em declaração do form... Vou precisar dos valores das variaveis "CorXX"

    qnd falei em mudar o label... referi-me ao label associado a cada combobox... Cada combobox tem um label para exibir a descrição. Pensei em fazer algo do tipo:

    Dim n As Long
    Dim cb As String


    Private Sub Completar()
    If ComboBox(n).Value <> vbNullString Then
           cb = ComboBox(n).Value
           Controls("Label" & n).Caption = Application.WorksheetFunction.VLookup(cb, Sheets("Dados").[A2:F302], 2, 0)
           Cor(n) = ComboBox(n).Value
          
      Else
           Controls("Label" & n).Caption = vbNullString
           Cor(n) = vbNullString' coloquei este if por causa de alterar o label da forma que te citei acima desta. creio que com controls("label"&n) não irá necessitar
      End If
    End Sub

    Private Sub ComboBox1_Change()
    n = 1
    Completar
    End Sub

     

    e assim por diante... tipo ate a variavel...

     

    Se souber de um jeito como fazer isso já me ajuda muito, Caso não tenha jeito já me economizou o If para VbNullString..

     

    Outra duvida minha seria se há como retirar dos comboboxs seguintes os intens já selecionados nos comboboxs anteriores... Para evitar duplicidade de itens... ou fazer um teste para saber se há algum combobox igual ao outro...

    Tentei usar isso:

    if Cor01<>Cor02<>Cor03<>Cor04<>Cor05<>Cor06 then

    else

    msgbox itens duplicados, vbcritical

    end if

    Mas o teste de diferença não foi aceito...

     

    Grande abraço e muito obrigado pela ajuda...

     

    • Marcado como Resposta Ilário Júnior segunda-feira, 23 de agosto de 2010 22:50
    segunda-feira, 23 de agosto de 2010 01:26

Todas as Respostas

  • Tens de fazer query ou melhor dizer pesquiza e o resultado coloca jno teu combobox e depois textbox.

     


    Just Be Humble Malange!
    domingo, 22 de agosto de 2010 16:51
  • Você pode fazer isso de várias formas. Uma simples:

    Você deve defirnir uma variável como

    Public strCritérioFormAnterior As String

    Abra o UserForm inicial normalmente, e atribua à essa variável o critério de filtro para adicionar itens no ComboBox do outro UserForm.

    E então, no novo UserForm, crie o código:

    Private Sub UserForm_Initialize()
      Const rOffset As Long = 2
      Dim rLast As Long
      Dim n As Long
      
      rLast = Cells(Rows.Count, "A").End(xlUp).Row
      
      For n = rOffset To rLast
        If Cells(n, "B") = strCritérioFormAnterior Then
          ComboBox1.AddItem Cells(n, "A") 'Só assim então adicionamos um valor à ComboBox1
        End If
      Next n
    End Sub

    Altere as colunas para chegar no resultado que desejar. Esse meu código considera que os dados adicionados ao UserForm estão na coluna A, os critérios na coluna B e que os dados tem apenas uma linha de cabeçalho.

    domingo, 22 de agosto de 2010 18:03
    Moderador
  • Olá Felipe,

    Consegui resolver o problema do combobox antes de tu postar. Fiz assim e deu certo:

     Private Sub Preenche()

        ComboBox1.Clear
        ComboBox2.Clear
        ComboBox3.Clear
        ComboBox4.Clear
        ComboBox5.Clear
        ComboBox6.Clear
        ComboBox7.Clear
        ComboBox8.Clear
        ComboBox9.Clear
        ComboBox10.Clear
        ComboBox11.Clear
        ComboBox12.Clear
        
        Sheets("Dados").Select
        Range("a1").Select
            Preencher = True
        Do While Preencher = True
                ActiveCell.Offset(1, 0).Select
           If ActiveCell.Value = vbNullString Then
                Preencher = False

    'LinhaProd é a variavel declara em modulo... cujo valor foi atribuido no outro Form
            ElseIf ActiveCell.Offset(0, 3).Value = LinhaProd Then
               ComboBox1.AddItem ActiveCell.Value
               ComboBox2.AddItem ActiveCell.Value
               ComboBox3.AddItem ActiveCell.Value
               ComboBox4.AddItem ActiveCell.Value
               ComboBox5.AddItem ActiveCell.Value
               ComboBox6.AddItem ActiveCell.Value
               ComboBox7.AddItem ActiveCell.Value
               ComboBox8.AddItem ActiveCell.Value
               ComboBox9.AddItem ActiveCell.Value
               ComboBox10.AddItem ActiveCell.Value
               ComboBox11.AddItem ActiveCell.Value
               ComboBox12.AddItem ActiveCell.Value
      
        End If
        Loop
                  
        End Sub

     

     

    Bem paracido com o teu, porém mais humilde...hehehe

    Mas cara, to precisando de uma maozinha em outro problema:

    No mesmo form... tenho os labels que seguem os comboboxs... tipo, se eu selecionar alguma coisa ele puxa a referencia da planilha e insere no label.Ex: seleciono SP 0200 no combobox, no evento change do combox coloquei vlookup e ele adiciona o valor BINDER no label 1

    Declarei a váriavel cb como string em estância de formulario

    Private Sub ComboBox1_Change()
      If ComboBox1.Value <> vbNullString Then
         cb = ComboBox1.Value
         FormulaCor_Form.Label1 = Application.WorksheetFunction.VLookup(cb, Sheets("Dados").[A2:F302], 2, 0)
       Else
         FormulaCor_Form.Label6 = vbNullString
      End If
    End Sub

     

    Mas teria que faze-lo para 12 combobox... e tenho outro form com 40 combobox....

    Teria algum modo de refinar a programação de modo que extraisse o nome do combobox selecionado(o número por exemplo) e inserisse na linha label(XXX)...ex

     FormulaCor_Form.Label(XXX) = Application.WorksheetFunction.VLookup(cb, Sheets("Dados").[A2:F302], 2, 0)

     e se pudesse me explicar o que significa os procedimentos que citaste... tentei entender, mas não consegui... Li uns exemplos de criar uma rotina para verificação de erros, era similar a tua, mas não deu certo e não entendi o porquê...

    Agradeço muito a ajuda e atenção!

     

    Aquele abraço e ótimo domingo!!!

     

    domingo, 22 de agosto de 2010 20:48
  • Seguem dois exemplos. Para executá-los, basta dar um clique num Userform com 5 Labels.

     

    Private Sub UserForm_Click()
      'Essa rotina renomeia todos os labels de um UserForm
      'Para testá-la, crie um UserForm com alguns labels.
      Dim ctrl As Control
      
      For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "Label" Then
          ctrl.Caption = "Texto de label alterado"
        End If
      Next ctrl
    End Sub

     

     

    Private Sub UserForm_Click()
      'Essa é uma alternativa ao método acima, que acho que é o que você quer.
      'Para testá-la, crie um UserForm com os Labels: Label1, Label2, Label3, Label4 e Label5.
      Dim n As Long
      
      For n = 1 To 5
        Controls("Label" & n).Caption = "Texto de label alterado"
      Next n
    End Sub

     

    Para retornar o nome de todos os controles um UserForm, use a rotina abaixo, por exemplo:

     

    Private Sub UserForm_Initialize()
      'Essa rotina renomeia todos os labels de um UserForm
      'Para testá-la, crie um UserForm com alguns labels.
      Dim ctrl As Control
      
      For Each ctrl In UserForm1.Controls
        Debug.Print ctrl.Name 'Os resultados serão mostrados na janela de Verificação imediata do VBE.
      Next ctrl
    End Sub

     

     

    domingo, 22 de agosto de 2010 21:04
    Moderador
  • A sua rotina simplificado, ficaria então:

    Private Sub Preenche()
    
      Dim k As Long
      Dim n As Long
      Dim nLast As Long
    
      With Sheets("Dados")
        nLast = .Cells(.Rows.Count, "A").End(xlUp).Row 'Ler última célula preenchida da coluna A e atribuir a nLast.
        For n = 1 To nLast
          For k = 1 To 12
            If n = 1 Then 'Se é a primeira vez que os ComboBox serã povoados, limpá-los.
              Controls("ComboBox" & k).Clear
            End If
            Controls("ComboBox" & k).AddItem .Cells(n, "A")
          Next k
        Next n
      End With
    End Sub

    Note que alterei seu método de buscar informações da planilha. Uma prática não recomendada em VBA é selecionar uma planilha e ler os dados. É possível ler os dados diretamente de uma planilha sem que elas estejam selecionadas. Exemplo: Você pode povoar um ComboBox com dados da Plan1 mesmo se a Plan2 é a planilha selecionada.

    • Marcado como Resposta Ilário Júnior segunda-feira, 23 de agosto de 2010 22:50
    domingo, 22 de agosto de 2010 21:15
    Moderador
  • Olá Felipe...

    Gostei do teu exemplo... de mudar a opção caption do label.

    Pensei em diminuir ainda mais se possivel minhas linhas. Os comandos que funcionaram (de modo singelo) forma estes:

    Private Sub ComboBox2_Change()
      If ComboBox2.Value <> vbNullString Then
           cb = ComboBox2.Value
           FormulaCor_Form.Label2 = Application.WorksheetFunction.VLookup(cb, Sheets("Dados").[A2:F302], 2, 0)
           Cor02 = ComboBox2.Value
          
      Else
           FormulaCor_Form.Label2 = vbNullString
           Cor02 = vbNullString
      End If
    End Sub
    Private Sub ComboBox3_Change()
      If ComboBox3.Value <> vbNullString Then
           cb = ComboBox3.Value
           FormulaCor_Form.Label3 = Application.WorksheetFunction.VLookup(cb, Sheets("Dados").[A2:F302], 2, 0)
           Cor03 = ComboBox3.Value
           TesteDuplicata
      Else
           FormulaCor_Form.Label3 = vbNullString
           Cor03 = vbNullString
      End If
    End Sub

    ---------- e assim por diante ate o combobox 12. declarei cb como string em declaração do form... Vou precisar dos valores das variaveis "CorXX"

    qnd falei em mudar o label... referi-me ao label associado a cada combobox... Cada combobox tem um label para exibir a descrição. Pensei em fazer algo do tipo:

    Dim n As Long
    Dim cb As String


    Private Sub Completar()
    If ComboBox(n).Value <> vbNullString Then
           cb = ComboBox(n).Value
           Controls("Label" & n).Caption = Application.WorksheetFunction.VLookup(cb, Sheets("Dados").[A2:F302], 2, 0)
           Cor(n) = ComboBox(n).Value
          
      Else
           Controls("Label" & n).Caption = vbNullString
           Cor(n) = vbNullString' coloquei este if por causa de alterar o label da forma que te citei acima desta. creio que com controls("label"&n) não irá necessitar
      End If
    End Sub

    Private Sub ComboBox1_Change()
    n = 1
    Completar
    End Sub

     

    e assim por diante... tipo ate a variavel...

     

    Se souber de um jeito como fazer isso já me ajuda muito, Caso não tenha jeito já me economizou o If para VbNullString..

     

    Outra duvida minha seria se há como retirar dos comboboxs seguintes os intens já selecionados nos comboboxs anteriores... Para evitar duplicidade de itens... ou fazer um teste para saber se há algum combobox igual ao outro...

    Tentei usar isso:

    if Cor01<>Cor02<>Cor03<>Cor04<>Cor05<>Cor06 then

    else

    msgbox itens duplicados, vbcritical

    end if

    Mas o teste de diferença não foi aceito...

     

    Grande abraço e muito obrigado pela ajuda...

     

    • Marcado como Resposta Ilário Júnior segunda-feira, 23 de agosto de 2010 22:50
    segunda-feira, 23 de agosto de 2010 01:26
  • Marca como respondida por favor.

    Obrigado.


    Just Be Humble Malange!
    segunda-feira, 23 de agosto de 2010 21:54
  • Olá pessoal.... Consegui simplificar minhas rotinas.... ao menos um pouquinho...hehehe

    Ficou assim...

     

    Dim cbv as long

    Dim cb as string

    Private Sub UserForm_Activate()

     For cbv = 1 To 12

      Limpa

      Preenche

     Next cbv

    End Sub

    Private Sub PreencheLabel()

     If Controls("ComboBox" & cbv).Value <> vbNullString Then

      cb = Controls("ComboBox" & cbv).Value

      Controls("Label" & cbv).Caption = Application.WorksheetFunction.VLookup(cb, Sheets("Dados").[A2:F302], 2, 0)

      TesteDuplicata

     Else

     Controls("Label" & cbv).Caption = vbNullString

     End If

    End Sub

     

    Private Sub UserForm_Activate()

     For cbv = 1 To 12

      Limpa

      Preenche

     Next cbv

    End Sub

     Private Sub Limpa()

      Controls("ComboBox" & cbv).Clear

     End Sub

    Private Sub Preenche()

        LinhaProd = "Star SP" 

    'defini LinhaProd como "Star SP" apenas para teste... essa var é definida no form anterior

        Sheets("Dados").Select

        Range("a1").Select

        Preencher = True

            Do While Preencher = True

              ActiveCell.Offset(1, 0).Select

               If ActiveCell.Value = vbNullString Then

                 Preencher = False

               ElseIf ActiveCell.Offset(0, 3).Value = LinhaProd Then

                 Controls("ComboBox" & cbv).AddItem ActiveCell.Value

               End If

            Loop

    End Sub

     

    Private Sub ComboBox1_Change()

      cbv = 1

      PreencheLabel

    End Sub

    Private Sub ComboBox2_Change()

      cbv = 2

      PreencheLabel

    End Sub

    Private Sub ComboBox3_Change()

      cbv = 3

      PreencheLabel

    End Sub

     

    e assim por diante.... Funcionou muito bem...

    Obrigado pela Força, tempo e conhecimento despendidos....

    Um grande abraço a todos.

    quarta-feira, 25 de agosto de 2010 02:36
  • Esse exemplo é o que você busca: http://spreadsheetpage.com/index.php/file/a_color_picker_dialog_box/

    Mas antes, terá que estudar módulos de classe: http://www.cpearson.com/Excel/Classes.aspx


    http://www.ambienteoffice.com.br
    sábado, 28 de agosto de 2010 03:58
    Moderador