A Userform to enter data on a new line

[Greg]

Hi all, I have prepared a simple spreadsheet to record debtor payments and determine the unpaid balance at any given time.However, I would like to add a Userform into which I can enter a payment amount, the date of payment and perhaps the interest rate. I say perhaps because the rate may change at a particular date and my spreadsheet is not sophisticated enough for that at this stage.I searched the knowledge base for assistance and found a listbox that I may be able to utilise. The problem is that I want to enter a new row each time I enter the new data and I want that row to contain the formulas necessary to do my calculation.Before I get into deep water can anyone suggest a simple way to achieve my desired outcome? Unfortunatley, I was unable to attach an example of my spreadsheet.Any help will be appreciated.Regards,Greg.

[mdmackillop]

[VBA]
Private Sub CommandButton1_Click()
Dim Rng As Range
With Sheets(1)
Set Rng = Range(.Cells(Rows.Count, 1).End(xlUp).Offset(1))
End With
Rng = ListBox1.Value
Rng.Offset(, 1) = TextBox1.Value
'etc.
End Sub
[/VBA]

[Greg]

Thanks MDM but I thought I'd try somehting else I found on this site. It is a macro that adds a row and copies data simply by making an entry in column A or in my case in column C (see attached).

However it is runs very slowly and doesn't really achieve the desired outcome. To make this work properly I need to be able to change both column C and column D (a date). Furthermore, I want to show the last date in column F as today's date.

Am I going about this the right way or this a much neater solution?

Any help is appreciated.

Regards,

Greg.

[lucas]

Hi Greg,
I just removed columns with no data from the array, down to the following....

[vba]
Cols = Array(4, 5, 6, 7, 8, 9, 10, 11)
[/vba]

It works much better now. It was looking in columns for data where none existed.

[Greg]

Thanks Lucas that's much better.

Is is possible to enter the next payment and the date of payment via a Userform and if so, how do I insert a bookmark that can be carried down to the next row when it is created?

What bothers me about what I am doing is that it can't be reversed as easily as it can be created. For example, if I made an error or I simply wanted to remove a number of lines, how do I do that via a Userform or can't it be done?

Greg.

[lucas]

Greg, If Malcolms code doesn't help then attached is an example of how to add data to the next line and a very inefficient example of how to clear the contents of the last row of data entered.......if it's close to what you are looking for we can clean it up ....a lot.

[mdmackillop]

For the first part, how about
[VBA]
Public Sub Worksheet_Change(ByVal Target As Range)
'setup to extablish column A as the Target Column to trigger the routine
Application.EnableEvents = False
With Target
If .Columns.Count > 3 Or .Row < 4 Or .Column <> 3 Then
GoTo Exits
Else
.Offset(-1, 1).Resize(2, 8).FillDown
End If
Exits:
End With
Application.EnableEvents = True
End Sub
[/VBA]

[lucas]

There are so many unessesary selections in that code that I will probably get in trouble when someone sees it. The other point I want to make is that it uses just textboxes now....it is an example.....combo's or date pickers can be used if it is similar to your need.

[mdmackillop]

or better
[VBA]
Option Explicit
Public Sub Worksheet_Change(ByVal Target As Range)
'setup to extablish column A as the Target Column to trigger the routine
With Target
If .Columns.Count > 3 Or .Row < 4 Or .Column <> 3 Then
Exit Sub
Else
Application.EnableEvents = False
.Offset(-1, 1).Resize(2, 8).FillDown
Application.EnableEvents = True
End If
End With
End Sub

[/VBA]

[mdmackillop]

Greg, If Malcolms code doesn't help then attached is an example of how to add data to the next line and a very inefficient example of how to clear the contents of the last row of data entered.......if it's close to what you are looking for we can clean it up ....a lot.

Attachment?

[lucas]

What else did I miss.......the fomula's.....sorry Malcolm, I will step aside.

[mdmackillop]

Carry on Steve. I'm off to work very shortly.

[Greg]

Thank you both. That is a tremendous improvement. I'll experiment for a while and see how far I can get without your further help.

[lucas]

Attachment?

I'm sorry Malcolm, I don't understand. I attached a file to the post you referenced...post #6

[mdmackillop]

I see it now.
Thanks Steve.

[mdmackillop]

There are so many unessesary selections in that code that I will probably get in trouble when someone sees it.

Without the Selection and Activations!
[vba]
Private Sub CommandButton3_Click()
Dim LastEntry As Range
With ActiveWorkbook.Sheets("Sheet1")
Set LastEntry = .Cells(Rows.Count, 1).End(xlUp)
End With
If LastEntry.Row > 1 Then
LastEntry.Resize(, 3).ClearContents
Else
MsgBox "You can't remove something that's not there!!!!!"
End If
End Sub
[/vba]

[Greg]

Well, I have had a play and made a few changes but I still need help. My amended spreadsheet is attached.

Firstly, I seem unable to enter the last date in column F. The entry defaults to column E instead.

Secondly, "removing the last entry" doesn't produce the required result. It did on the sample you provided but in my case some other unrelated cell is chosen for removal.

I seem destined to make a mess of this and would be grateful if you were to provide a little more guidance.

Regards,

Greg.

[mdmackillop]

[vba]
Option Explicit

Private Sub CommandButton1_Click()
Dim LastRow As Range
Dim Response As Long

Set LastRow = Range("C" & Rows.Count).End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = CDate(TextBox2.Text)
LastRow.Offset(1, 2).Value = "-"
LastRow.Offset(1, 3).Value = CDate(TextBox3.Text)
LastRow.Offset(0, 4).Resize(2, 5).FillDown
MsgBox "One record written to Sheet1"
Response = MsgBox("Do you want to enter another record?", _
vbYesNo)
If Response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End Sub

Private Sub CommandButton2_Click()
End
End Sub

Private Sub CommandButton3_Click()
Dim LastEntry As Range

With ActiveSheet
Set LastEntry = .Cells(Rows.Count, 3).End(xlUp)
End With
If LastEntry.Row > 21 Then
LastEntry.Resize(, 9).ClearContents
Else
MsgBox "You can't remove something that's not there!!!!!"
End If
End Sub

[/vba]

[lucas]

Dang, you guys get up early.

Malcolm has fixed it for you. You could easily add the following to the userform code so the todays date will load automatically in textbox 3

[VBA]
Private Sub UserForm_Initialize()
TextBox3.Text = Date
End Sub
[/VBA]

[Greg]

Well guys, thank you for getting up so early. I have one last question.

As you probably realise, the date in column F on any given row should always be one (1) day less than the date in column D of the following row. The exception is that the very last date entered should be today's date. I have no idea how to construct that argument. Do you mind helping one more time?

Thanks in advance,

Greg.