Apply button - Visible cells

Question

I have a form where there is a Listview, and textbox for data input and already by login I can filter a column, to appear only the fields I want.

Where is the problem? the intention is, I click on the line of Listview, inputar the values of predicted and realized and this apply, put this value both in Listview (What is working) and Add to filtered line in excel (that’s the problem)

It does not pull the cell I selected, it always plays in the 1 excel cell.. so in this case, I believe I need somehow this button to insert only value into the visible cell that I selected.

Follow the form print and apply button code!

Private Sub CommandButton5_Click()
    Sheets("Treinamentos Tableau").Select

    Dim PosiçãoAtual As Variant
    PosiçãoAtual = Treinamentos.SelectedItem.Index + 1

    Cells(PosiçãoAtual, 3).Value = TextBox6.Text
    Cells(PosiçãoAtual, 4).Value = TextBox7.Text


    With Treinamentos
        .SelectedItem.SubItems(2) = TextBox6.Text
        .SelectedItem.SubItems(3) = TextBox7.Text
    End With   

End Sub

---------------- EDIT, EXPLANATION WITH PRINTS ---------------

Explanation 1 : I logged in and password, with a unit and it filtered only that unit, correct? soon I will click on the line I want, put the values of predicted and realized in the textbox and click apply.

Explanation 2 : I clicked to apply, in listview, he was in the right place, show, is in Taquari, December as was the line I selected

Explanation 3 : When I go in the excel spreadsheet, it does not pull the filtered line that I entered the value, it is going to the line that I did not Filtrei, that in case so picking up the invisible lines after I apply the filter

Answer 1

Problem

You are entering the position of the Listview table in the "Tableau Trainings" worksheet, but, this is not the one that should be sought. Because the Listview indexes change when filtering, that is, they are not the same as the spreadsheet.

Solution

Loop the visible cells of the worksheet and check that the "E" column is equal to the selected value Treinamentos.SelectedItem.SubItems(1). If yes, fill in columns "C" and "D" with the values of TextBox6.Text and TextBox7.Text, respectively.

Code

This example code has the Autofilter part for the unit, however, as this filter is already performed. This part can be removed from the code.

The explanation of the code is commented on the same.

Sub teste()
    Dim ws As Worksheet
    Dim unidade As String, data As String
    Dim UltimaLinha As Long, linha As Long
    Dim VisibleRange As Range
    
    Set ws = ThisWorkbook.Sheets("Treinamentos Tableau")
    'Inserir unidade, no seu caso o valor para filtrar
    unidade = "b"
    
    With ws
        'Encontra a ultima linha da coluna A
        UltimaLinha = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        'Filtro Unidade
        .Range("A1").AutoFilter Field:=1, Criteria1:=unidade
        
        On Error Resume Next
        Set VisibleRange = .Range(.Cells(2, 1), .Cells(UltimaLinha, 1)).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        If Not VisibleRange Is Nothing Then
            'Loop nas células visíveis após aplicação do AutoFiltro
            For Each Cell In VisibleRange
                'Linha
                linha = Cell.Row
                'Verifica se a coluna 5, ou seja, coluna Data é igual ao selecionado
                If Treinamentos.SelectedItem.SubItems(1) = .Cells(linha, "E") Then
                    'Se sim, escreve na célula de mesma linha
                    .Cells(linha, 3).Value = TextBox6.Text
                    .Cells(linha, 4).Value = TextBox7.Text
                End If
            Next
        End If
        '"zera" o autofiltro, mostrando todos dados filtrados
        If .FilterMode Then
            .ShowAllData
        End If
    End With
End Sub