data inserted in rows disappears

**ncaravela** · 10-08-2010, 12:53 PM

Hi everybody,

I have a Wsheet with 8 textboxes. The first 5, when saving the data to the wsheet from the userform has the following code:

ActiveCell.Offset(0, 3) = TextBox5

The last 3 textboxes have a different code:

ActiveCell.Offset(1, 5) = TextBox9

My objective is that the data from the 3 last textboxes goes to the three rows below and, when i enter a new record, the data goes to the row below the row where the textbox11 was inserted.

My problem is that, when i insert a new record, the data from those 3 textboxes dissapeare.

Can anyone help me with this?

I think that the attach file should explain it better

**pike** · 10-09-2010, 01:30 AM

Hi ncaravela

try change the select code to

ThisWorkbook.Sheets("Base Dados").Activate
        Sheets("Base Dados").Cells(Rows.Count, "F").End(xlUp).Offset(1, -5).Select
        Do

**pike** · 10-09-2010, 01:41 AM

or this

Private Sub cmdguardar_Click()
  Dim txtid As Long
   txtid = valmax + 1
       With ThisWorkbook.Sheets("Base Dados").Cells(Rows.Count, "F").End(xlUp)
       .Offset(1, -5) = txtid.Value
        .Offset(1, -4) = txtdataregisto
        .Offset(1, -3) = TextBox5
         .Offset(1, -2) = TextBox6
         .Offset(1, -1) = TextBox7
         .Offset(1, 0) = TextBox8
         .Offset(2, 0) = TextBox9
        .Offset(3, 0) = TextBox10
         .Offset(4, 0) = TextBox11
    End With
      Range("A9").Select
      
      Unload Me
 End Sub

**ncaravela** · 10-14-2010, 07:39 AM

Pike,

Thanks for your help. However, there's a detail that i forgot to mencione.
Not in every record i need to fill in the three textboxes (9, 10 and 11).
Sometimes i just need to fill in txtbox9, sometimes 9 and 10...

When using your code, if i don't fill in the three txtboxes, i get one or two blank rows.
I've tryed adding the following code, to delete blank rows, in amodule, but it's not working.

Private Sub Worksheet_Change(ByVal Target As Range)
'Deletes blank rows as they occur.
    'Prevent endless loops
    Application.EnableEvents = False
    'They have more than one cell selected
    If Target.Cells.Count > 1 Then GoTo SelectionCode
        If WorksheetFunction.CountA(Target.EntireRow) = 0 Then
            Target.EntireRow.Delete
        End If

    Application.EnableEvents = True
    'Our code will only enter here if the selection is more than one cell.
    Exit Sub

SelectionCode:
    If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
        Selection.EntireRow.Delete
    End If
    Application.EnableEvents = True
End Sub

can you help me?