[SOLVED:] Saving is not creating a new record line, but saving over it [Archive]

View Full Version : [SOLVED:] Saving is not creating a new record line, but saving over it

jrsilverio

10-25-2022, 05:46 PM

When I click save it doesn't create a new line, it just saves over the top.

Private Sub CommandButton4_Click()

Dim ws As Worksheet
Dim LastRow As Long, x As Long
Set ws = ThisWorkbook.Sheets("Planilha0")
Dim erro As String

erro = ""

'ComboBox2
If Len(ComboBox2) = 0 Then
erro = "Campo de ID inválido" & vbNewLine
End If

'ComboBox3
If Not IsDate(ComboBox3) Then
erro = erro & "Campo de Data inválido" & vbNewLine
End If

'ComboBox4
If Len(ComboBox4) = 0 Then
erro = erro & "Campo da Natureza de Nota Fiscal inválido" & vbNewLine
End If

'ComboBox5
If Len(ComboBox5) = 0 Then
erro = erro & "Campo da Nota Fiscal inválido" & vbNewLine
End If

'ComboBox6
If Len(ComboBox6) = 0 Then
erro = erro & "Campo de Relação de Remessa inválido" & vbNewLine
End If

'ComboBox7
If Len(ComboBox7) = 0 Then
erro = erro & "Campo de Unidade Requisitante inválido!!!"
End If

'ComboBox8
If Len(ComboBox8) = 0 Then
erro = erro & "Campo de Comprador inválido!!!"
End If

'ComboBox8
If Len(ComboBox10) = 0 Then
erro = erro & "Campo de Recebedor inválido!!!"
End If

'ComboBox8
If Len(ComboBox11) = 0 Then
erro = erro & "Campo de Recebimento inválido!!!"
End If

'Error?
If erro <> "" Then
MsgBox erro, vbCritical, "Campo(s) com Erros"
ComboBox4.SetFocus
Exit Sub
End If

With ws
x = 8
Do While .Cells(x, 1) <> ""
x = x + 1
Loop
LastRow = x '.Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(LastRow, 2).Value2 = ComboBox2.Value
.Cells(LastRow, 3).Value2 = CDate(ComboBox3.Value)
.Cells(LastRow, 4).Value2 = ComboBox4.Value
.Cells(LastRow, 5).Value2 = ComboBox5.Value
.Cells(LastRow, 6).Value2 = ComboBox6.Value
.Cells(LastRow, 7).Value2 = ComboBox7.Value
.Cells(LastRow, 8).Value2 = ComboBox8.Value
.Cells(LastRow, 9).Value2 = ComboBox10.Value
.Cells(LastRow, 10).Value2 = ComboBox11.Value
.Cells(LastRow, 11).Value2 = OptionButton2.Value
'---UseForm3---'
'.Cells(LastRow, 12).Value2 = CheckBox1.Value
'.Cells(LastRow, 13).Value2 = CheckBox2.Value
'.Cells(LastRow, 14).Value2 = CheckBox3.Value
'.Cells(LastRow, 15).Value2 = CheckBox4.Value
'.Cells(LastRow, 16).Value2 = CheckBox5.Value
'.Cells(LastRow, 17).Value2 = CheckBox6.Value
'--------------'
.Cells(LastRow, 22).Value2 = TextBox1.Value
End With

ComboBox4.SetFocus

Call MsgBox("Parabéns!" + vbCrLf + "Cadastro realizado com sucesso." + vbCrLf + vbCrLf + _
"IDº : " & ComboBox2, vbExclamation, "Aviso")

End Sub

arnelgp

10-25-2022, 10:40 PM

try:

...
...
With ws
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
if lastrow < 9 then
lastrow = 8
end if
lastrow = lastrow + 1
.Cells(LastRow, 2).Value2 = ComboBox2.Value
.Cells(LastRow, 3).Value2 = CDate(ComboBox3.Value)
.Cells(LastRow, 4).Value2 = ComboBox4.Value
.Cells(LastRow, 5).Value2 = ComboBox5.Value
.Cells(LastRow, 6).Value2 = ComboBox6.Value
.Cells(LastRow, 7).Value2 = ComboBox7.Value
.Cells(LastRow, 8).Value2 = ComboBox8.Value
.Cells(LastRow, 9).Value2 = ComboBox10.Value
.Cells(LastRow, 10).Value2 = ComboBox11.Value
.Cells(LastRow, 11).Value2 = OptionButton2.Value
'---UseForm3---'
'.Cells(LastRow, 12).Value2 = CheckBox1.Value
'.Cells(LastRow, 13).Value2 = CheckBox2.Value
'.Cells(LastRow, 14).Value2 = CheckBox3.Value
'.Cells(LastRow, 15).Value2 = CheckBox4.Value
'.Cells(LastRow, 16).Value2 = CheckBox5.Value
'.Cells(LastRow, 17).Value2 = CheckBox6.Value
'--------------'
.Cells(LastRow, 22).Value2 = TextBox1.Value
End With
...
...

p45cal

10-26-2022, 01:48 AM

You're looking at column 1 (column A) to find the next empty row, but in your routine which writes to the sheet you don't put anything in column 1!
So either write something to column 1 or look for the first empty cell in another column, say column 2:
Do While .Cells(x, 2) <> ""

jrsilverio

10-26-2022, 02:02 AM

Created a new line with the registration, for the next ones they still keep overwriting

try:

...
...
With ws
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
if lastrow < 9 then
lastrow = 8
end if
lastrow = lastrow + 1
.Cells(LastRow, 2).Value2 = ComboBox2.Value
.Cells(LastRow, 3).Value2 = CDate(ComboBox3.Value)
.Cells(LastRow, 4).Value2 = ComboBox4.Value
.Cells(LastRow, 5).Value2 = ComboBox5.Value
.Cells(LastRow, 6).Value2 = ComboBox6.Value
.Cells(LastRow, 7).Value2 = ComboBox7.Value
.Cells(LastRow, 8).Value2 = ComboBox8.Value
.Cells(LastRow, 9).Value2 = ComboBox10.Value
.Cells(LastRow, 10).Value2 = ComboBox11.Value
.Cells(LastRow, 11).Value2 = OptionButton2.Value
'---UseForm3---'
'.Cells(LastRow, 12).Value2 = CheckBox1.Value
'.Cells(LastRow, 13).Value2 = CheckBox2.Value
'.Cells(LastRow, 14).Value2 = CheckBox3.Value
'.Cells(LastRow, 15).Value2 = CheckBox4.Value
'.Cells(LastRow, 16).Value2 = CheckBox5.Value
'.Cells(LastRow, 17).Value2 = CheckBox6.Value
'--------------'
.Cells(LastRow, 22).Value2 = TextBox1.Value
End With
...
...

snb

10-26-2022, 03:59 AM

Avoid redundant variables.
The simpler the better:

ws.Cells(Rows.Count, 1).End(xlUp).offset(1,1).resize(,10)=array(ComboBox2,ComboBox3,ComboBox4,Co mboBox5,ComboBox6,ComboBox7,ComboBox8,ComboBox10,ComboBox11,OptionButton2)

p45cal

10-26-2022, 04:04 AM

Probably safer to tweak that to:
ws.Cells(Rows.Count, 2).End(xlUp).offset(1).resize(,10)=array(ComboBox2…

jrsilverio

10-26-2022, 01:58 PM

Thank you all.
5 stars

jrsilverio

10-27-2022, 03:45 PM

Probably safer to tweak that to:
ws.Cells(Rows.Count, 2).End(xlUp).offset(1).resize(,10)=array(ComboBox2…

There's a detail that I didn't pay attention to before finishing the topic, when I select OPTIONBUTTON or OPTIONBUTTON2, it saves the CAPTION in the same cell, and the other CHECKBOX I put to save the caption, but it saves in the cell, even not selecting the fields.

Private Sub CommandButton4_Click()

Dim ws As Worksheet
Dim LastRow As Long, x As Long
Set ws = ThisWorkbook.Sheets("Planilha0")
Dim erro As String

erro = ""

'ComboBox2
If Len(ComboBox2) = 0 Then
erro = "Campo de ID inválido" & vbNewLine
End If

'ComboBox3
If Not IsDate(ComboBox3) Then
erro = erro & "Campo de Data inválido" & vbNewLine
End If

'ComboBox4
If Len(ComboBox4) = 0 Then
erro = erro & "Campo da Natureza de Nota Fiscal inválido" & vbNewLine
End If

'ComboBox5
If Len(ComboBox5) = 0 Then
erro = erro & "Campo da Nota Fiscal inválido" & vbNewLine
End If

'ComboBox6
If Len(ComboBox6) = 0 Then
erro = erro & "Campo de Relação de Remessa inválido" & vbNewLine
End If

'ComboBox7
If Len(ComboBox7) = 0 Then
erro = erro & "Campo de Unidade Requisitante inválido" & vbNewLine
End If

'ComboBox8
If Len(ComboBox8) = 0 Then
erro = erro & "Campo de Comprador inválido" & vbNewLine
End If

'ComboBox10
If Len(ComboBox10) = 0 Then
erro = erro & "Campo de Recebedor inválido" & vbNewLine
End If

'ComboBox11
If Len(ComboBox11) = 0 Then
erro = erro & "Campo de Recebimento inválido" & vbNewLine
End If

'Error?
If erro <> "" Then
MsgBox erro, vbCritical, "Campo(s) com Erros"
ComboBox4.SetFocus
Exit Sub
End If

With ws
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If LastRow < 9 Then
LastRow = 8
End If
LastRow = LastRow + 1
ws.Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(, 17) = Array(ComboBox2, ComboBox3, ComboBox4, ComboBox5, ComboBox6, ComboBox7, ComboBox8, ComboBox10, ComboBox11, OptionButton2.Caption, CheckBox1.Caption, CheckBox2.Caption, CheckBox3.Caption, CheckBox4.Caption, CheckBox5.Caption, CheckBox6.Caption, TextBox1)
'.Cells(LastRow, 10).Value2 = OptionButton1.value
End With

ComboBox4.SetFocus

Call MsgBox("Parabéns!" + vbCrLf + "Cadastro realizado com sucesso." + vbCrLf + vbCrLf + _
"IDº : " & ComboBox2, vbExclamation, "Aviso")

Unload UserForm2
UserForm2.Show

End Sub

30288

30289

snb

10-28-2022, 12:25 AM

Check your VBA handbook again:

these lines are 100% redundant:

LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If LastRow < 9 Then
LastRow = 8
End If
LastRow = LastRow + 1

So: remove them.

It's not a bad idea to try to understand the VBA proposals that have been made before applying them.

jrsilverio

10-28-2022, 02:41 PM