Usuário com melhor resposta
filtrar entrada em combobox e vlookup/hlookup em textbox

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
- Editado Ilário Júnior domingo, 22 de agosto de 2010 14:59 add inf
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
-
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 SubPrivate Sub ComboBox1_Change()
n = 1
Completar
End Sube 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
Todas as Respostas
-
-
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.
-
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 SubBem 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 SubMas 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!!!
-
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
-
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
-
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 SubPrivate Sub ComboBox1_Change()
n = 1
Completar
End Sube 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
-
-
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.
-
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