filtrar entrada em combobox e vlookup/hlookup em textbox

Question

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

Answer 1

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

 

---

Just Be Humble Malange!

Answer 2

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.

Answer 3

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!!!

 

Answer 4

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

 

 

Answer 5

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.

Answer 6

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...

 

Answer 7

Marca como respondida por favor.

Obrigado.

---

Just Be Humble Malange!

Answer 8

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.

Answer 9

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