VBA Excel 2010 User Form Money Calculator Issues

[killiouta]

I am writing a program in VBA Excel 2010. The program is basically a self service concession stand which allows for certain members to keep a tab (negative balance), but other users must pay.

There a user can type in his or her ID numbers, and items name, balance, and purchase history will populate from the workbook sheet 1.

The user can then click buttons to add values($.25, $.50, etc), which will add to the total.

They can then choose to pay, or add to their current balance.

I'm new to VB/VBA, I am having a few issues:

First, the balances are not calculating like I need them. The program needs to correctly handle the sign of the balance (positive or negative) during the calculation.

Next, the pay button is not updating the balance.

Also, I have multiple values (column D) in a single cell with a comma delimiter. I want to populate these values to the listbox if possible.

Last, I want to transfer the new variables data back to the spreadsheet before save.

Here a link to the workbook:
https://www.dropbox.com/s/6shdx0pwnf77606/consession.xlsm

and here is my code:



'enter ID button
Public Sub mavid_btn_Click()
mavID = Consession.id_txt.Text
Dim i As Integer
i = 1
Sheets("Sheet1").Activate
'interate through rows until match an populate data in user form
Do While Cells(i, 1).Value <> ""
If Cells(i, 1).Value = mavID Then
name = Cells(i, 2).Value
balance = Cells(i, 3).Value
Consession.name_txt.Text = name
Consession.bal_txt.Text = VAL(balance)
total = 0
'text box formatting
Consession.total_txt.Text = VAL(total)
Consession.total_txt.Font.name = "Arial"
Consession.total_txt.Font.Size = 14
Consession.pay_txt.Font.name = "Arial"
Consession.pay_txt.Font.Size = 14
i = i + 1
Else
i = i + 1
End If
Loop
End Sub
'add total to balance button
Public Sub addtobal_btn_Click()
If balance > 0 Then
balance = VAL(balance) - VAL(total)
Else
balance = VAL(balance) + VAL(total)
End If
Consession.bal_txt.Text = balance
End Sub
'pay button
Public Sub pay_btn_Click()
If balance > 0 Then
balance = VAL(balance) - VAL(payment)
Else
balance = VAL(balance) + VAL(payment)
End If
Consession.bal_txt.Text = balance
End Sub


'cancel button
Public Sub cancel_btn_Click()


'clear form
Dim ctl As MSForms.Control
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.Value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl
End Sub


Public Sub done_btn_Click()
'save workbook
ActiveWorkbook.Save


'clear form
Dim ctl As MSForms.Control


For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.Value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select

Next ctl
End Sub


'---PRICE BUTTONS------------------------------------




Public Sub add25_btn_Click()
'add 0.25 to total_txt
total = VAL(total) + 0.25
Consession.total_txt.Text = total
End Sub


Public Sub add50_btn_Click()
'add 0.50 to total_txt
total = VAL(total) + 0.5
Consession.total_txt.Text = total
End Sub


Public Sub add75_btn_Click()
'add 0.75 to total_txt
total = VAL(total) + 0.75
Consession.total_txt.Text = total
End Sub


Public Sub add1_btn_Click()
'apend 1.00 to total_txt
total = VAL(total) + 1
Consession.total_txt.Text = total
End Sub


Public Sub add150_btn_Click()
'append 1.50 to total_txt
total = VAL(total) + 1.5
Consession.total_txt.Text = total
End Sub


Public Sub add2_btn_Click()
'append 2 to total_txt
total = VAL(total) + 2
Consession.total_txt.Text = total
End Sub