Excel 2007 : Payments Made User Form with Text Boxes and Option Buttons

[shelz2pou]

Hi there,

I'm creating a User Form using Excel 2007 in which the user enters an amount through text box (txtamt1) and based on which option button (credit card("opbuttccard"), b-pay("opbuttbpay"), post office("opbuttpoffice") or other("opbuttother")) the information from txtamt1 is submitted to workbook ("Ptp_Workbook"). Because the user will be using the spreadsheet constantly, the information inputted needs to be calculated as the information is updated (depending on option button selected). For example: $123 (txtamt1) and opbuttccard selected and saved, then next lot of data was $345 (txtamt1) and opbuttccard selected, on the workbook the amount in (cell.B2) should show a figure $468 and so on. The code that I have places the amount in the right cell but doesn't calculate further data that's inputted. I would also like to know if there is any way that when the user closes and saves the information collected for the day, when they open the document the next time, it shows a message box on how much $$$ they collected when the document was closed (shut down).

I hope I have made sense
Thanks heaps
Shelz

[pike]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[shelz2pou]

Hi Pike

Thanks for the response. Here is the codes for the Excel Document that I'm using: In the actual spreadsheet it's pretty straight forward - A1 = Date A2 Credit Card A3 B-Pay A4 Other

Thanks once again for your help

Private Sub cmdbuttcont_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Ptp_Worksheet")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
'copy the data to the database
With ws
    .Cells(iRow, 1).Value = Me.TxtDate.Value
End With
End Sub
Private Sub opbuttccard_click()
Range("B2") = Me.txtboxamt1.Value
End Sub
Private Sub opbuttbpay_click()
Range("C2") = Me.txtboxamt1.Value
End Sub
Private Sub opbuttpoffice_click()
Range("D2") = Me.txtboxamt1.Value
End Sub
Private Sub opbuttother_click()
Range("E2") = Me.txtboxamt1.Value
End Sub
Private Sub UserForm_Initialize()
Me.TxtDate.Value = Format(Date, "Medium Date")
End Sub

[shelz2pou]

Sorry about that

Private Sub cmdbuttcont_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Ptp_Worksheet")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
'copy the data to the database
With ws
    .Cells(iRow, 1).Value = Me.TxtDate.Value
End With
End Sub

Private Sub opbuttccard_click()
Range("B2") = Me.txtboxamt1.Value
End Sub

Private Sub opbuttbpay_click()
Range("C2") = Me.txtboxamt1.Value
End Sub

Private Sub opbuttpoffice_click()
Range("D2") = Me.txtboxamt1.Value
End Sub

Private Sub opbuttother_click()
Range("E2") = Me.txtboxamt1.Value
End Sub

Private Sub UserForm_Initialize()
Me.TxtDate.Value = Format(Date, "Medium Date")
End Sub

[pike]

Hi shelz2pou
do you mean to add the values to the cells value

Private Sub opbuttbpay_click()
Range("C2") = Me.txtboxamt1.Value + Range("C2") .value
End Sub

[shelz2pou]

Yes thanks heaps for this