Here's one for the experts

The Program listing below works fine, except that I need the Date formatted as a date and the invoice cost as a currency. If I preformat the sheet prior to running the UserForm then the usedrange sees them as filled cells and skips them, so, if I format from A2 to a100 the first entry goes in at A1 and the second at A101.

Dim lCurrentRow As Long


Private Sub cmdPrevious_Click()
If lCurrentRow > 3 Then
SaveRow
lCurrentRow = lCurrentRow - 1
LoadRow
End If
End Sub

Private Sub cmdNext_Click()
SaveRow
lCurrentRow = lCurrentRow + 1
LoadRow
End Sub

Private Sub cmdDelete_Click()
Dim sMessage As String
sMessage = "Are You Sure You Want To Delete " + txtInvNum.Text + "?"
If MsgBox(sMessage, vbQuestion + vbYesNo, "Confirm Delete") = vbYes Then
Rows(lCurrentRow).Delete
LoadRow
End If
End Sub

Private Sub cmdAdd_Click()
SaveRow
If Cells(lCurrentRow, 1).Value = "" Then
lCurrentRow = 3
Else
lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1
End If
LoadRow
txtDate.SetFocus
End Sub

Private Sub UserForm_Activate()
lCurrentRow = 3
LoadRow
End Sub

Private Sub cmdClose_Click()
SaveRow
Unload Me
End Sub

Private Sub LoadRow()

txtDate.Text = Cells(lCurrentRow, 1).Value
txtInvNum.Text = Cells(lCurrentRow, 2).Value
txtInvSup.Text = Cells(lCurrentRow, 3).Value
txtInvCost.Text = Cells(lCurrentRow, 4).Value
End Sub

Private Sub SaveRow()
Cells(lCurrentRow, 1).Value = txtDate.Text
Cells(lCurrentRow, 2).Value = txtInvNum.Text
Cells(lCurrentRow, 3).Value = txtInvSup.Text
Cells(lCurrentRow, 4).Value = txtInvCost.Text
End Sub


I ran a macro to see what was needed to format the cells from within the routine an added the this as a sub routine with the ell range modified so:-

Sub Fomat()
Range(Cells(lCurrentRow, 1)).Select
Selection.NumberFormat = "d/mm/yyyy;@"
Range(Cells(lCurrentRow, 4)).Select
Selection.NumberFormat = "[$£-809]#,##0.00;[Red][$£-809]#,##0.00"
End Sub

It just does not work. Anyone any idea why please?

I saw you use, in the 'Private Sub cmdAdd_Click()' sub the code:

lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1

to get the first free row.

You may try this:

lCurrentRow = ActiveSheet.range("A3").CurrentRegion.Rows.Count + 3 'because you start from row 3

or you can also try with this:

lCurrentRow = ActiveSheet.Range("a65536").End(xlUp).Row + 1

and so you can preformat your sheet.

Regards,
Antonio