PDA

View Full Version : A Userform to enter data on a new line



Greg
07-09-2008, 10:10 PM
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
07-09-2008, 11:57 PM
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

Greg
07-13-2008, 09:44 PM
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
07-13-2008, 10:12 PM
Hi Greg,
I just removed columns with no data from the array, down to the following....


Cols = Array(4, 5, 6, 7, 8, 9, 10, 11)


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

Greg
07-13-2008, 10:47 PM
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
07-13-2008, 11:07 PM
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
07-13-2008, 11:08 PM
For the first part, how about

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

lucas
07-13-2008, 11:10 PM
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
07-13-2008, 11:10 PM
or better

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

mdmackillop
07-13-2008, 11:11 PM
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
07-13-2008, 11:11 PM
What else did I miss.......the fomula's.....sorry Malcolm, I will step aside.

mdmackillop
07-13-2008, 11:13 PM
Carry on Steve. I'm off to work very shortly.

Greg
07-13-2008, 11:14 PM
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
07-13-2008, 11:14 PM
Attachment?
I'm sorry Malcolm, I don't understand. I attached a file to the post you referenced...post #6

mdmackillop
07-13-2008, 11:16 PM
I see it now.
Thanks Steve.

mdmackillop
07-13-2008, 11:22 PM
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!

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

Greg
07-14-2008, 02:15 AM
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
07-14-2008, 04:25 AM
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

lucas
07-14-2008, 09:57 AM
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


Private Sub UserForm_Initialize()
TextBox3.Text = Date
End Sub

Greg
07-14-2008, 06:55 PM
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.

lucas
07-14-2008, 08:20 PM
I'm missing something....how do we come up with a date in column d of the following row if no data has been input there?

mdmackillop
07-14-2008, 11:40 PM
Remove Textbox3

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 = Date
'Change previous last date
LastRow.Offset(0, 3).Value = CDate(TextBox2.Text) - 1
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 = ""

TextBox1.SetFocus
Else
Unload Me
End If
End Sub

Greg
07-15-2008, 12:28 AM
Bloody brilliant but I have one last question.

How do I get the totals at J10 and J11 to alter when payments are added or subtracted?

In a Word table I'd have to use Bookmarks enclosing the top and bottom cells but I expect that Excel is much simpler than that. I know my way around Word but Excel is still a bit of a mystery to me.

Once more, your help is greatly appreciated.

Regards,

Greg.

Greg
07-15-2008, 01:22 AM
Sorry, just one more thing. I notice that the added/deleted rows don't carry forward the correct formulas for columns I, J & K. I'm not asking you to do it for me but I would appreciate your guidance.

In the attached example I have made the adjustments manually (rightly or wrongly) but I need to automate those formulas so that they don't alter when a row is added or removed. I'm not sure how to do that yet.

mdmackillop
07-15-2008, 03:45 AM
With a formula such as
=$H$6-SUM(C25:C32)+SUM(J25:J32)
you need to make the start of the sum range absolute as
=$H$6-SUM(C$25:C32)+SUM(J$25:J32)

With regard to totals, you can create Dynamic Ranges using the Offset function (you can do a search for those here). In this case, I've adjusted the code to insert the required formulae.

Greg
07-15-2008, 08:50 PM
Thanks once more. I get the idea now and should be able to refine this on my own from here.