excelvbalistboxuserformxlsm

VBA Save (Add) Data Duplicate Items on ListBox, but only Save One (as it should)


I'm trying to develop a plan, and when I'm saving a item, the listbox add the new item, but duplicates the other data aready saved, duplicating the itens.

Salve code: Private Sub btnSalvar_Click()

If txtReceita.Value = "" Then
    MsgBox ("Insira o nome da receita"), vbInformation, "Cadastro de Receitas"
    Exit Sub
End If

If btnModificar.Value = True Then
    nlin = listaReceitas.ListIndex
    If nlin = -1 Then
        MsgBox "Selecione uma receita para editar", vbInformation, "Cadastro de Receitas"
        Exit Sub
    ElseIf listaReceitas.Value = 0 Then
        MsgBox "Selecione uma receita para editar", vbInformation, "Cadastro de Receitas"
        Exit Sub
    End If
    Call modificar

Else
linha = Sheets("NOMES RECEITAS").Range("B1000000").End(xlUp).Row + 1

Sheets("NOMES RECEITAS").Cells(linha, 2).Value = WorksheetFunction.Max(Sheets("NOMES RECEITAS").Range("B:B")) + 1
Sheets("NOMES RECEITAS").Cells(linha, 3).Value = txtReceita.Value
    txtReceita.Value = ""
    
MsgBox "Receita adicionada com sucesso!", vbInformation, "Cadastro de Receitas"
    End If
Call Atualizar_Receitas
End Sub
Display ListBox Code: Sub Atualizar_Receitas()

Dim rg As Range
Dim linf As Integer
'Dim wPlan As Worksheet
Dim vLin As Integer
   
    Set wPlan = Planilha10
    vLin = Application.WorksheetFunction.CountA(Planilha10.Range("C:C"))
    Set rg = wPlan.Range("C2:C" & vLin)
    For linf = 1 To rg.Rows.Count
               
        With Me.listaReceitas
            '.ColumnWidths = "45;110;30;95"
            .ColumnCount = 2
            .AddItem
            .List(listaReceitas.ListCount - 1, 1) = rg.Cells(linf, 1)
        End With
    Next
Me.lblContador.Caption = Me.listaReceitas.ListCount & " Receita(s)"
End Sub

Plan with all items

Form when start - showing correct number of items = 11

Form after saving "new item" - showing duplicate of the others item + the new one, total items = 23, but the plan only have 12 items

I've watched a lot of videos, but to no success. I'd appreciate any help I can get.


Solution

  • I was able to make it work by adding +1 at the row count.

    Sub Atualizar_Receitas()
    
        On Error GoTo Erro
        
        Dim rg As Range
        Dim linf As Integer
        
        Dim vLin As Integer
           
            Set wPlan = Planilha10
            vLin = Application.WorksheetFunction.CountA(Planilha10.Range("C:C"))
            Set rg = wPlan.Range("C2:C" & vLin)
            Me.listaReceitas.Clear
            For linf = 1 To rg.Rows.Count + 1
                       
                With Me.listaReceitas
                    .ColumnCount = 2
                    .AddItem
                    .List(listaReceitas.ListCount - 1, 0) = rg.Cells(linf, 0)
                    .List(listaReceitas.ListCount - 1, 1) = rg.Cells(linf, 1)
                End With
            Next
        Me.lblContador.Caption = Me.listaReceitas.ListCount & " Receita(s)"
        
        Exit Sub
        Erro:
        MsgBox "Ocorreu um Erro!", vbCritical, "ERRO - ListBox"
    End Sub