Record data in cell of a vba excel table

Question

I have a userform to record a data in the table Tabelaviagens. In the form I select the trip in the combobox and to register the data I use event in the button approve. However the code is not working, always passing to Msgbox. I checked the value that the matrix(i, 1) is receiving, and always refers to the value of the last record in column 1. For some reason cb_viagem.Value passes this value.

How to correct?

Following file. I shorten the database to facilitate the tests. But still can not identify the problem. Download

Private Sub Aprovar_Click()
    ActiveSheet.Unprotect Password:="gpq"

    If cb_viagem.Value = "" Then
        MsgBox "Selecione a Viagem!!"
    Else


        Dim ws As Worksheet
        Dim tbl As ListObject
        Dim i As Long
        Dim matriz As Variant
        Set ws = ThisWorkbook.Sheets("Planejamento") 'ou para declarar com o nome da planilha: ThisWorkbook.Sheets("Agosto")
        Set tbl = ws.ListObjects("TabelaViagens") 'No seu caso: .ListObjects("Atividades")
        With tbl
            matriz = .DataBodyRange              'Cria matriz

            For i = UBound(matriz) To LBound(matriz) Step -1
                If matriz(i, 1) = cb_viagem.Value And matriz(i, 28) = 0 Then

                    matriz(i, 28).Value = "1"
                    matriz(i, 29).Value = "Aprovado"

                    ' .ListRows(i).Delete

                    Exit For

                Else: MsgBox "Viagem já foi aprovada ou negada!"
                    Exit For
                End If
            Next i
        End With

    End If
End Sub

Answer 1

It seems to me you have a problem inside your for loop . I suppose the idea was to iterate the rows of the matrix from the bottom up, looking for the corresponding trip number.

This is not the case because the loop is finished in the first iteration since there are instructions Exit For both in the conditions if as else. Therefore, case matriz(Ubound(matriz), 1) (last entry in column 1) is not equal to cb_viagem.Value (selected value of the trip), the routine ends with the message you are seeing.

The routine Aprovar_Click() corrected would be:

Private Sub Aprovar_Click()

ActiveSheet.Unprotect Password:="gpq"
If cb_viagem.Value = "" Then
    MsgBox "Selecione a Viagem!!"
 Else


Dim ws As Worksheet
Dim tbl As ListObject
Dim i As Long
Dim matriz As Variant
Set ws = ThisWorkbook.Sheets("Planejamento")                         'ou para declarar com o nome da planilha: ThisWorkbook.Sheets("Agosto")
Set tbl = ws.ListObjects("TabelaViagens")   'No seu caso: .ListObjects("Atividades")
With tbl
    matriz = .DataBodyRange                   'Cria matriz

    For i = UBound(matriz) To LBound(matriz) Step -1
        If Trim(matriz(i, 1)) = cb_viagem.Value Then
          If Trim(matriz(i, 30)) = 0 Then

            With ws.ListObjects("TabelaViagens")
              .DataBodyRange(i, 30).Value = 1
              .DataBodyRange(i, 31).Value = "Aprovada"
            End With

          Else: MsgBox "Viagem já foi aprovada ou negada!"""
          End If
          Exit For
        End If
    Next i
End With

End If



ActiveSheet.Protect Password:="gpq"


End Sub

And the routine Delete_Click() (that you said you were failing) corrected would be:

Private Sub Delete_Click()
ActiveSheet.Unprotect Password:="gpq"
If CB_Viagens.Value = "" Then
    MsgBox "Selecione a Viagem!!"
 Else


Dim ws As Worksheet
Dim tbl As ListObject
Dim i As Long
Dim matriz As Variant
Set ws = ThisWorkbook.Sheets("Planejamento")                         'ou para declarar com o nome da planilha: ThisWorkbook.Sheets("Agosto")
Set tbl = ws.ListObjects("TabelaViagens")   'No seu caso: .ListObjects("Atividades")
With tbl
    matriz = .DataBodyRange                   'Cria matriz

    For i = UBound(matriz) To LBound(matriz) Step -1
        If Trim(matriz(i, 1)) = CB_Viagens.Value Then

          If matriz(i, 28) = NomeUsuario() Then

            .ListRows(i).Delete

          Else: MsgBox "Você não tem permissão para excluir essa viagem. Apenas quem criou o registro pode excluir!"
          End If

          Exit For
        End If
    Next i
End With

NOTE: I had to change some column indexes of the variable matriz to match the spreadsheet you provided.