Ir ao conteúdo
  • Cadastre-se
Regras do Clube do Hardware

Excel Vincular combobox e listbox a uma tabela dinâmica (excel - vba)

Jefferson TS

Por Jefferson TS
em Microsoft Office e similares

 Compartilhar
Ir à solução Resolvido por RafaVillani,

Posts recomendados

Jefferson TS
 

Jefferson TS

Postado
Postado

Olá. Tenho um formulário para cadastrar recebimento de mensalidades. Preciso fazer o listbox deste formulário trazer o histórico de pagamentos do cliente selecionado no combobox (nome / mês / valor).
Talvez a melhor forma seja vinculando a uma tabela dinâmica da base de dados, que traz como filtro o cliente e mostra os valores pagos em cada mê

Alguém pode me ajudar com isto? Seguem as imagens abaixo:

image.png.d17446e1904d3c990a6f329634f22791.png

image.png.2e235830d640145eefc3c5ae18a2c0e7.png

Link para o comentário
Compartilhar em outros sites
RafaVillani
 

RafaVillani

Postado
Postado

@Jefferson TSA aba filtro recebe as informações filtradas da aba BASE conforme a seleção do combobox clientes, e estas informações filtradas são a base para carregar o listbox, em resumo, é onde o filtro é feito. 

 

Sempre que o combobox é alterado a célula M2 recebe o valor do combobox, bem como a Sub Filtro é ativada, onde ocorre o filtro conforme o nome da célula M2, as informações são salvas no Range e depois carregadas no listbox.

 

RafaVillani

  • Curtir 1
Link para o comentário
Compartilhar em outros sites
RafaVillani
 

RafaVillani

Postado
Postado

@Jefferson TSOlá, este erro ocorre por duas razões, primeiro porque você esta tentando filtrar as informação da aba BASE, quando deveria filtrar os dados da aba CONVENIÊNCIA, na Sub FiltroFiado substitua essa linha

  

Set base = Planilha1.Range("A4").CurrentRegion

 

por essa

  

Set base = Plan1.Range("A4").CurrentRegion

 

segundo porque você esta inserindo o nome do cliente na célula errada, a célula M2 da aba FILTRO_FIADO corresponde à quantidade, e não ao Cliente (Célula K2), dentro do comboboxCLIENTE_FIADO  substitua essa linha

  

Planilha5.Range("M2") = ComboBoxCLIENTE_FIADO.Value

 

por esta

  

Planilha5.Range("K2") = ComboBoxCLIENTE_FIADO.Value

 

Dessa forma irá funcionar.

 

RafaVillani

  • Curtir 1
Link para o comentário
Compartilhar em outros sites
Jefferson TS
 

Jefferson TS

Postado
  • Autor
Postado

@RafaVillani show. Deu certinho.
Eu ia criar um novo tópico, mas talvez seja mais fácil pra você responder isto já que já mexeu neste projeto.

Preciso selecionar as linhas desejadas da ListBoxFiado para quando clicar no botão baixar, seja lançada a data atual na base de dados (na coluna data de pagamento).
E ainda, na textboxTOTALFIADO seja somado em tempo real as linhas selecionadas da listbox.

Link para o comentário
Compartilhar em outros sites
RafaVillani
 

RafaVillani

Postado
Postado

@Jefferson TSVamos lá, primeiro altere algumas propriedades do UserFormBAIXAR_FIADO e do ListBoxFIADO, para configura-los corretamente, conforme abaixo.

 

UserFormBAIXAR_FIADO

propriedade Width coloque 580

 

ListBoxFIADO

propriedade ColumnWidths coloque 60 pt;100 pt;49.95 pt;60 pt;75 pt;55 pt;49.95 pt

propriedade MultiSelect coloque 1-fmMultiSelectMulti

prorpiedade Width coloque 545

 

6 horas atrás, Jefferson TS disse:

Preciso selecionar as linhas desejadas da ListBoxFiado para quando clicar no botão baixar, seja lançada a data atual na base de dados (na coluna data de pagamento).

 

Dentro do evento Click do botao Baixar cole este código:

  

Dim i As Integer, L As Long, cont As Integer
Dim linha As Long
cont = 0
linha = Plan1.Range("A4").CurrentRegion.Rows.Count + 3
For i = 0 To ListBoxFIADO.ListCount - 1
    If ListBoxFIADO.Selected(i) = True Then
           For L = 6 To linha
                If Plan1.Cells(L, 1) = ListBoxFIADO.List(i, 0) And Plan1.Cells(L, 2) = ListBoxFIADO.List(i, 1) _
                And Plan1.Cells(L, 3) = ListBoxFIADO.List(i, 2) And Plan1.Cells(L, 4) = ListBoxFIADO.List(i, 3) _
                And Plan1.Cells(L, 5) = ListBoxFIADO.List(i, 4) And Plan1.Cells(L, 6) = ListBoxFIADO.List(i, 5) Then
                    Plan1.Cells(L, 7) = Date
                    cont = 1
                End If
            Next
    End If
Next
If cont = 1 Then
    Call FiltroFiado
End If

 

6 horas atrás, Jefferson TS disse:

E ainda, na textboxTOTALFIADO seja somado em tempo real as linhas selecionadas da listbox.

 

Crie uma nova Sub, duplo clique no UserFormBAIXAR_FIADO e cole este código:

  

Sub Soma_Selecao()

Dim Item As Integer
Dim Soma As Double
Soma = 0

For Item = 0 To ListBoxFIADO.ListCount - 1
    If ListBoxFIADO.Selected(Item) = True And IsNumeric(ListBoxFIADO.List(Item, 5)) = True Then
        Soma = Soma + ListBoxFIADO.List(Item, 5)
    End If
Next
TextBoxTOTALFIADO = Format(Soma, "R$ #,##0.00")
    
End Sub

 

Dentro do envento MouseUp do ListBoxFiado cole este código:

  

Call Soma_Selecao

 

Uma dica, altere o local de armazenamento inicial da sua ID para que essa linha não apareça nos listbox, tanto na aba Base quanto na aba CONVENIÊNCIA, apenas questão de estética,  elas podem ser inicializadas em qualquer célula da planilha, K1 por exemplo, não precisa estar dentro da tabela.

 

RafaVillani

 

Link para o comentário
Compartilhar em outros sites
Jefferson TS
 

Jefferson TS

Postado
  • Autor
Postado

@RafaVillani Show. Deu certo. Apenas precisei colocar o nome do userform antes da listbox e textbox. Muito obrigado.
Uma última coisa. Sabe me dizer como trazer para o listbox apenas linhas que não tenham data de pagamento, ou seja, trazer apenas os devedores?

 

E tem como deixar o cabeçalho da listbox não selecionável? ou Então que não apareça, então colocaria label's na userform para representar o cabeçalho (fora da listbox)

Link para o comentário
Compartilhar em outros sites
RafaVillani
 

RafaVillani

Postado
Postado

@Jefferson TS

1 hora atrás, Jefferson TS disse:

E tem como deixar o cabeçalho da listbox não selecionável?

Tem sim, faça as seguintes alterações:

 

No evento Initialize do UserFormBAIXAR_FIADO insira este código abaixo da linha TextBoxDATA_FIADO = Date

  

L = Plan1.Range("A4").CurrentRegion.Rows.Count + 3

Set base = Plan1.Range(Plan1.Cells(5, 1), Plan1.Cells(L, 7))

NOME = "'" & Plan1.Name & "'!"
    
ListBoxFIADO.RowSource = NOME & base.Address
ListBoxFIADO.ColumnCount = 7


ListBoxFIADO.ColumnHeads = True

If L = 1 Then
    ListBoxFIADO.ColumnHeads = False
End If

 

E substitua todo o código da Sub FiltroFiado por este:

  

Dim base As Range
Dim crt As Range
Dim filtrada As Range
Dim NOME As String
Dim L As Long

Set base = Plan1.Range("A4").CurrentRegion
Set crt = Planilha5.Range("J1:P2")

base.AdvancedFilter xlFilterCopy, crt, Planilha5.Range("A4:G4")
L = Planilha5.Range("A4").CurrentRegion.Rows.Count

Set filtrada = _
Planilha5.Range(Planilha5.Cells(5, 1), Planilha5.Cells(L + 3, 7))
NOME = "'" & Planilha5.Name & "'!"
    
UserFormBAIXAR_FIADO.ListBoxFIADO.RowSource = NOME & filtrada.Address

If L = 1 Then
    UserFormBAIXAR_FIADO.ListBoxFIADO.ColumnHeads = False
End If

 

Isso fará o cabeçalho ficar fixo.

 

1 hora atrás, Jefferson TS disse:

Sabe me dizer como trazer para o listbox apenas linhas que não tenham data de pagamento, ou seja, trazer apenas os devedores?

Tem como sim, mas teriamos que alterar a forma como os dados são carregados na listbox, e isso atrapalharia o Filtro, para contornar isso, aconselho você criar uma nova aba, "Pagos\Baixados" por exemplo, e ao clicar no botão Baixar a data é inserida e os dados baixados enviados para a planilha "Pagos\Baixados", dessa forma o listbox sempre irá listar apenas os devedores.

 

RafaVillani

Link para o comentário
Compartilhar em outros sites
Jefferson TS
 

Jefferson TS

Postado
  • Autor
Postado

@RafaVillani  o cabeçalho fixo deu certinho, obrigado.

 

13 horas atrás, RafaVillani disse:

ao clicar no botão Baixar a data é inserida e os dados baixados enviados para a planilha "Pagos\Baixados"

 

Criei a 'Plan2 (Conveniência_Pagos)', desculpe a ignorância, mas como eu referencio isto para enviar os pagos para a nova plan?

  

Private Sub CommandButtonBAIXAR_Click()
Dim i As Integer, L As Long, cont As Integer
Dim linha As Long
cont = 0
linha = Plan1.Range("A4").CurrentRegion.Rows.Count + 3
For i = 0 To ListBoxFIADO.ListCount - 1
    If ListBoxFIADO.Selected(i) = True Then
           For L = 6 To linha
                If Plan1.Cells(L, 1) = ListBoxFIADO.List(i, 0) And Plan1.Cells(L, 2) = ListBoxFIADO.List(i, 1) _
                And Plan1.Cells(L, 3) = ListBoxFIADO.List(i, 2) And Plan1.Cells(L, 4) = ListBoxFIADO.List(i, 3) _
                And Plan1.Cells(L, 5) = ListBoxFIADO.List(i, 4) And Plan1.Cells(L, 6) = ListBoxFIADO.List(i, 5) Then
                    Plan1.Cells(L, 7) = Date
                    cont = 1
                End If
            Next
    End If
Next
If cont = 1 Then
    Call FiltroFiado
End If
End Sub

 

 

CONTROLE NOVO - Copia.rar

Link para o comentário
Compartilhar em outros sites
RafaVillani
 

RafaVillani

Postado
Postado

@Jefferson TSDentro do botão baixar crie uma nova variável: Dim linhapago As Long

 

e, entre as linhas de comando Plan1.Cells(L, 7) = Date e cont = 1, cole este código:

  

linhapago = Plan2.Range("A4").CurrentRegion.Rows.Count + 4
Plan2.Cells(linhapago, 1) = Plan1.Cells(L, 1)
Plan2.Cells(linhapago, 2) = Plan1.Cells(L, 2)
Plan2.Cells(linhapago, 3) = Plan1.Cells(L, 3)
Plan2.Cells(linhapago, 4) = Plan1.Cells(L, 4)
Plan2.Cells(linhapago, 5) = Plan1.Cells(L, 5)
Plan2.Cells(linhapago, 6) = Plan1.Cells(L, 6)
Plan2.Cells(linhapago, 7) = Plan1.Cells(L, 7)
Plan1.Rows(L).Delete

 

RafaVillani

  • Curtir 1
Link para o comentário
Compartilhar em outros sites
RafaVillani
 

RafaVillani

Postado
Postado

@Jefferson TSConsegui melhorar o filtro por parte do nome, agora ele funciona com o cabeçalho fixo, se lhe interessar basta substituir todo o código da Sub FiltroParte por esse: 

Dim linhamatriz As Long, Linha As Long
Dim ultimalinha As Range
Dim fonte(1 To 500, 1 To 6) As Variant
Dim Cont As Integer
Dim L As Long
linhamatriz = 1
Linha = 6
Cont = 4
Me.ListBoxFIADO.RowSource = Empty
Me.ListBoxFIADO.ColumnHeads = True
L = Planilha5.Range("A5").CurrentRegion.Rows.Count + 4
Planilha5.Range("A5:F" & L).ClearContents
Set ultimalinha = Plan1.Range("A1000000").End(xlUp)
Plan1.Select
With Plan1
    Do While Linha <= ultimalinha.Row
        If UCase(.Cells(Linha, 2)) Like UCase("*" & (TextBox1) & "*") Then
                fonte(linhamatriz, 1) = .Cells(Linha, 1)
                fonte(linhamatriz, 2) = .Cells(Linha, 2)
                fonte(linhamatriz, 3) = .Cells(Linha, 3)
                fonte(linhamatriz, 4) = .Cells(Linha, 4)
                fonte(linhamatriz, 5) = .Cells(Linha, 5)
                fonte(linhamatriz, 6) = .Cells(Linha, 6)
                Cont = Cont + 1
                linhamatriz = linhamatriz + 1
        End If
        Linha = Linha + 1
    Loop
End With
If Cont = 4 Then
    Me.ListBoxFIADO.RowSource = Planilha5.Range("A5:F5").Address
    Exit Sub
End If
Planilha5.Select
Planilha5.Range("A5:F" & Cont) = fonte
Me.ListBoxFIADO.RowSource = Planilha5.Range("A5:F" & Cont).Address

RafaVillani

  • Curtir 1
Link para o comentário
Compartilhar em outros sites

Crie uma conta ou entre para comentar

Você precisa ser um usuário para fazer um comentário

Criar uma conta

Crie uma nova conta em nossa comunidade. É fácil!

Crie uma nova conta

Entrar

Já tem uma conta? Faça o login.

Entrar agora
 Compartilhar
Ir à lista de tópicos

Sobre o Clube do Hardware

No ar desde 1996, o Clube do Hardware é uma das maiores, mais antigas e mais respeitadas comunidades sobre tecnologia do Brasil. Leia mais

Direitos autorais

Não permitimos a cópia ou reprodução do conteúdo do nosso site, fórum, newsletters e redes sociais, mesmo citando-se a fonte. Leia mais

Mais

×
×
  • Criar novo...

Ebook grátis: Aprenda a ler resistores e capacitores!

EBOOK GRÁTIS!

CLIQUE AQUI E BAIXE AGORA MESMO!