Logo Hardware.com.br
eduarda schwarzer
eduarda schw... Novo Membro Registrado
3 Mensagens 0 Curtidas

Passar dados de formulário para listview

#1 Por eduarda schw... 09/03/2018 - 07:58
Bom dia, estou com um problemão e não tenho ideia de como resolver.
Estou montando uma planilha com vários dados, no começo é feito um formulário para alimentar uma planilha que contenha todos os dados, em segundo abro um listview para procurar esses dados, porém quando os dados são cadastrados pelo formulário, o listview não os aceita e não procura esses dados.

Alguém poderia me ajudar?

Segue meu código

Private Sub EXPORTAR_Click()



Plan3.Select



Dim inicio As Range



Set inicio = Plan3.Range("A1&quot



    With Plan1

   

        For i = 1 To ListView1.ListItems.Count

       

inicio.Cells(i + 1, 1) = ListView1.ListItems(i).Text

inicio.Cells(i + 1, 2) = ListView1.ListItems(i).SubItems(1)

inicio.Cells(i + 1, 3) = ListView1.ListItems(i).SubItems(2)

inicio.Cells(i + 1, 4) = ListView1.ListItems(i).SubItems(3)

inicio.Cells(i + 1, 5) = ListView1.ListItems(i).SubItems(4)

inicio.Cells(i + 1, 6) = ListView1.ListItems(i).SubItems(5)

inicio.Cells(i + 1, 7) = ListView1.ListItems(i).SubItems(6)

inicio.Cells(i + 1, 8) = ListView1.ListItems(i).SubItems(7)

    

   Next

  

End With





End Sub



Private Sub TextBox1_Change()



Dim Abas As Worksheet

Dim linha As Integer

Dim xPesq As String

Dim xCel As String

Dim Regs As Integer

Dim xRegs As Integer

Dim xPlan As String

Dim LV



Plan1.Activate

xPlan = Plan1.Name

xRegs = Range("A" & Rows.Count).End(xlUp).Row



xPesq = TextBox1:

linha = 2



Set Abas = ThisWorkbook.Worksheets(xPlan)

    With Abas

   

        Me.ListView1.ListItems.Clear

        While .Cells(linha, 1) <> Empty

       

            coluna = 1

            xCel = Cells(linha, coluna)

           

            If InStr(1, UCase(xCel), UCase(xPesq), 1) Then

           

                Set LV = ListView1.ListItems.Add(Text:=Plan1.Cells(linha, 1))

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 2)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 3)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 4)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 5)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 6)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 7)

                       

                        GoTo Proxima_linha

                        

          End If

         

         

         

Proxima_linha:

       

            linha = linha + 1

           

           

            Wend

           

    End With

    Set LV = Nothing

   



End Sub



Private Sub TextBox2_Change()





Dim Abas As Worksheet

Dim linha As Integer

Dim xPesq As String

Dim xCel As String

Dim Regs As Integer

Dim xRegs As Integer

Dim xPlan As String

Dim LV



Plan1.Activate

xPlan = Plan1.Name

xRegs = Range("A" & Rows.Count).End(xlUp).Row



xPesq = TextBox2:

linha = 2



Set Abas = ThisWorkbook.Worksheets(xPlan)

    With Abas

   

        Me.ListView1.ListItems.Clear

        While .Cells(linha, 1) <> Empty

       

            coluna = 2

            xCel = Cells(linha, coluna)

           

            If InStr(1, UCase(xCel), UCase(xPesq), 5) Then

           

                Set LV = ListView1.ListItems.Add(Text:=Plan1.Cells(linha, 1))

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 2)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 3)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 4)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 5)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 6)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 7)

                        GoTo Proxima_linha

                        

          End If

         

         

Proxima_linha:

       

            linha = linha + 1

           

           

            Wend

           

    End With

    Set LV = Nothing

 

End Sub



Private Sub TextBox3_Change()



Dim Abas As Worksheet

Dim linha As Integer

Dim xPesq As String

Dim xCel As String

Dim Regs As Integer

Dim xRegs As Integer

Dim xPlan As String

Dim LV



Plan1.Activate

xPlan = Plan1.Name

xRegs = Range("A" & Rows.Count).End(xlUp).Row



xPesq = TextBox3:

linha = 2



Set Abas = ThisWorkbook.Worksheets(xPlan)

    With Abas

   

        Me.ListView1.ListItems.Clear

        While .Cells(linha, 1) <> Empty

       

            coluna = 3

            xCel = Cells(linha, coluna)

           

            If InStr(1, UCase(xCel), UCase(xPesq), 5) Then

           

                Set LV = ListView1.ListItems.Add(Text:=Plan1.Cells(linha, 1))

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 2)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 3)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 4)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 5)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 6)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 7)

                        GoTo Proxima_linha

                        

          End If

         

         

Proxima_linha:

       

            linha = linha + 1

           

           

            Wend

           

    End With

    Set LV = Nothing

  



End Sub



Private Sub TextBox4_Change()



Dim Abas As Worksheet

Dim linha As Integer

Dim xPesq As String

Dim xCel As String

Dim Regs As Integer

Dim xRegs As Integer

Dim xPlan As String

Dim LV



Plan1.Activate

xPlan = Plan1.Name

xRegs = Range("A" & Rows.Count).End(xlUp).Row



xPesq = TextBox4:

linha = 2



Set Abas = ThisWorkbook.Worksheets(xPlan)

    With Abas

   

        Me.ListView1.ListItems.Clear

        While .Cells(linha, 1) <> Empty

       

            coluna = 4

            xCel = Cells(linha, coluna)

           

            If InStr(1, UCase(xCel), UCase(xPesq), 5) Then

           

                Set LV = ListView1.ListItems.Add(Text:=Plan1.Cells(linha, 1))

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 2)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 3)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 4)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 5)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 6)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 7)

                        GoTo Proxima_linha

                        

          End If

         

         

Proxima_linha:

       

            linha = linha + 1

           

           

            Wend

           

    End With

    Set LV = Nothing

   

End Sub



Private Sub TextBox5_Change()



Dim Abas As Worksheet

Dim linha As Integer

Dim xPesq As String

Dim xCel As String

Dim Regs As Integer

Dim xRegs As Integer

Dim xPlan As String

Dim LV



Plan1.Activate

xPlan = Plan1.Name

xRegs = Range("A" & Rows.Count).End(xlUp).Row



xPesq = TextBox5:

linha = 2



Set Abas = ThisWorkbook.Worksheets(xPlan)

    With Abas

   

        Me.ListView1.ListItems.Clear

        While .Cells(linha, 1) <> Empty

       

            coluna = 5

            xCel = Cells(linha, coluna)

           

            If InStr(1, UCase(xCel), UCase(xPesq), 5) Then

           

                Set LV = ListView1.ListItems.Add(Text:=Plan1.Cells(linha, 1))

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 2)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 3)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 4)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 5)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 6)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 7)

                        GoTo Proxima_linha

                        

          End If

         

         

Proxima_linha:

       

            linha = linha + 1

           

           

            Wend

           

    End With

    Set LV = Nothing

   



End Sub



Private Sub TextBox6_Change()



Dim Abas As Worksheet

Dim linha As Integer

Dim xPesq As String

Dim xCel As String

Dim Regs As Integer

Dim xRegs As Integer

Dim xPlan As String

Dim LV



Plan1.Activate

xPlan = Plan1.Name

xRegs = Range("A" & Rows.Count).End(xlUp).Row



xPesq = TextBox6:

linha = 2



Set Abas = ThisWorkbook.Worksheets(xPlan)

    With Abas

   

        Me.ListView1.ListItems.Clear

        While .Cells(linha, 1) <> Empty

       

            coluna = 6

            xCel = Cells(linha, coluna)

           

            If InStr(1, UCase(xCel), UCase(xPesq), 5) Then

           

                Set LV = ListView1.ListItems.Add(Text:=Plan1.Cells(linha, 1))

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 2)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 3)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 4)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 5)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 6)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 7)

                        GoTo Proxima_linha

                        

          End If

         

         

Proxima_linha:

       

            linha = linha + 1

           

           

            Wend

           

    End With

    Set LV = Nothing

   

End Sub



Private Sub TextBox7_Change()



Dim Abas As Worksheet

Dim linha As Integer

Dim xPesq As String

Dim xCel As String

Dim Regs As Integer

Dim xRegs As Integer

Dim xPlan As String

Dim LV



Plan1.Activate

xPlan = Plan1.Name

xRegs = Range("A" & Rows.Count).End(xlUp).Row



xPesq = TextBox7:

linha = 2



Set Abas = ThisWorkbook.Worksheets(xPlan)

    With Abas

   

        Me.ListView1.ListItems.Clear

        While .Cells(linha, 1) <> Empty

       

            coluna = 7

            xCel = Cells(linha, coluna)

           

            If InStr(1, UCase(xCel), UCase(xPesq), 5) Then

           

                Set LV = ListView1.ListItems.Add(Text:=Plan1.Cells(linha, 1))

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 2)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 3)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 4)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 5)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 6)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 7)

                        GoTo Proxima_linha

                        

          End If

         

         

Proxima_linha:

       

            linha = linha + 1

           

           

            Wend

           

    End With

    Set LV = Nothing

   

End Sub



Private Sub TextBox8_Change()



Dim Abas As Worksheet

Dim linha As Integer

Dim xPesq As String

Dim xCel As String

Dim Regs As Integer

Dim xRegs As Integer

Dim xPlan As String

Dim LV



Plan1.Activate

xPlan = Plan1.Name

xRegs = Range("A" & Rows.Count).End(xlUp).Row



xPesq = TextBox8:

linha = 2



Set Abas = ThisWorkbook.Worksheets(xPlan)

    With Abas

   

        Me.ListView1.ListItems.Clear

        While .Cells(linha, 1) <> Empty

       

            coluna = 8

            xCel = Cells(linha, coluna)

           

            If InStr(1, UCase(xCel), UCase(xPesq), 5) Then

           

                Set LV = ListView1.ListItems.Add(Text:=Plan1.Cells(linha, 1))

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 2)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 3)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 4)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 5)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 6)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 7)

                        LV.ListSubItems.Add Text:=Plan1.Cells(linha, 8)

                       

                        GoTo Proxima_linha

                        

          End If

         

         

Proxima_linha:

       

            linha = linha + 1

           

           

            Wend

           

    End With

    Set LV = Nothing



End Sub



Private Sub UserForm_Initialize()



Me.ListView1.ListItems.Clear

    With ListView1



    .Gridlines = True

    .View = lvwReport

    .FullRowSelect = True

   

        .ColumnHeaders.Add(, , "Código", Width:=40, Alignment:=0).Tag = "number"

        .ColumnHeaders.Add(, , "SO", Width:=40, Alignment:=0).Tag = ""

        .ColumnHeaders.Add(, , "DATA", Width:=60, Alignment:=0).Tag = "date"

        .ColumnHeaders.Add(, , "ANALISTA", Width:=200, Alignment:=0).Tag = ""

        .ColumnHeaders.Add(, , "DESCRIÇÃO", Width:=200, Alignment:=0).Tag = ""

        .ColumnHeaders.Add(, , "CLIENTE", Width:=200, Alignment:=0).Tag = ""

        .ColumnHeaders.Add(, , "MATERIAL", Width:=200, Alignment:=0).Tag = ""

        .ColumnHeaders.Add(, , "HIPERLINK", Width:=200, Alignment:=0).Tag = ""

                      

End With



    Dim linha As Long

    Dim xCont As Long

    Dim x

    Dim LV

      

   

        Plan1.Activate

        xCont = Plan1.Range("A" & Rows.Count).End(xlUp).Row

        Me.ListView1.ListItems.Clear

       

    For linha = 2 To xCont

      

            Set LV = ListView1.ListItems.Add(Text:=Plan1.Cells(linha, 1))

                LV.ListSubItems.Add Text:=Plan1.Cells(linha, 2)

                LV.ListSubItems.Add Text:=Plan1.Cells(linha, 3)

                LV.ListSubItems.Add Text:=Plan1.Cells(linha, 4)

                LV.ListSubItems.Add Text:=Plan1.Cells(linha, 5)

                LV.ListSubItems.Add Text:=Plan1.Cells(linha, 6)

                LV.ListSubItems.Add Text:=Plan1.Cells(linha, 7)

                LV.ListSubItems.Add Text:=Plan1.Cells(linha, 8)

                                     

      Next linha

     

      REGISTRO.Caption = ListView1.ListItems.Count



     

      Set LV = Nothing

     

   lbl_registros = xCont - 1 & "Registros Listados"

       



End Sub
pesquisar formulario listview vba dados planilha formulario listview
© 1999-2024 Hardware.com.br. Todos os direitos reservados.
Imagem do Modal