[RESOLVED] Payment Form

[oh-sorry]

Hello guys

i am Creating a monthly payment receive form from Microsoft excel vba

here is the screenshot of the form and excel sheet




in this form i want is when i enter the Customer ID it will check if the customer has paid to that month and the checkboxes will be ticked if customer has paid

after that i will tick for unpaid month and the form will calculate how many i have checked and it will add How many months i am paying to the text box and when i click pay it will update the excel


Exsample:

when i enter Customer ID = 1, the form will show that Customer has paid to "JAN,FEB,MAR,APR,MAY"
the checkboxes from JAN TO MAY will be Ticked

now i want to pay for JUNE and JULY
when i TICK JUNE and JULY the Textbox for HOW MANY MONTHS will Be 2
after that i will click pay and the sheet will be updated for that customer

This is the Code i am Using Now

Dim i As Long
Dim x As Double

For i = 2 To Sheet1.Range("A100000").End(xlUp).Row
If Sheet1.Cells(i, 1) = TextBox4.Text Then
a = Val(Me.TextBox6) / Val(Me.TextBox1)
Me.TextBox5 = CDate(Me.TextBox5)
m = Format(Me.TextBox5, "M")
For b = m + 1 To m + a
Sheet1.Cells(i, b + 2) = Me.TextBox1.Value
Next b

End If
Next i

Sheet2.Range("A1000000").End(xlUp).Offset(1, 0).Value = Date
Sheet2.Range("A1000000").End(xlUp).Offset(0, 1).Value = Me.TextBox4.Value
Sheet2.Range("A1000000").End(xlUp).Offset(0, 2).Value = Me.TextBox6.Value


Me.TextBox1 = "100"
Me.TextBox2 = ""
Me.TextBox3 = ""

[westconn1]

it is quite difficult to know which textbox on the form is which, while sometimes there is advantages in using generic names for controls (see below). other times it is better to use meaningful names

me.textbox1 = "100"
for i = 1 to 12
   if  me.controls("checkbox" & i).value = true then cnt = cnt +  1   ' this is where generic names can be an advantage, change the name of the checkboes if required
next
me.textbox2 = cnt
me.textbox3 = me.textbox1 * me.textbox2

as this only gets the count of checked values, you would need to loop through again to assign the values to the cells, else you could store the checked boxes to an array and assign the array to the range when you click pay

[oh-sorry]

Thank you westconn1,

these are the details of the Textboxes

ID = TextBox4
House Name = TextBox2
Name = TextBox3
Fee = TextBox1 (100/- Per Month)
How Many Months = TextBox5
Amount = TextBox6

[westconn1]

these are the details of the Textboxes

that was not what i guessed, but you should be able to change the textbox numbers to make it work correctly

[oh-sorry]

westconn1 thank you dear

The code you gave me works fine. but the problem is i use a code when i enter customer id to check whether the customer has paid to that month if the customer has paid to the month the check boxes will be ticked this is the code i am using now.

For i = 2 To Sheet1.Range("D100000").End(xlUp).Row
If Sheet1.Cells(i, 1) = TextBox4.Text Then
If Sheet1.Cells(i, 4) = "100" Then
CheckBox1.Value = True ' If Month "January" is Paid
Else
CheckBox1.Value = False
End If
If Sheet1.Cells(i, 5) = "100" Then
CheckBox2.Value = True ' If Month "Feb" is Paid
Else
CheckBox2.Value = False
End If
If Sheet1.Cells(i, 6) = "100" Then
CheckBox3.Value = True ' If Month "MAR" is Paid
Else
CheckBox3.Value = False
End If
If Sheet1.Cells(i, 7) = "100" Then
CheckBox4.Value = True ' If Month "APR" is Paid
Else
CheckBox4.Value = False
End If
If Sheet1.Cells(i, 8) = "100" Then
CheckBox5.Value = True ' If Month "MAY" is Paid
Else
CheckBox5.Value = False
End If
If Sheet1.Cells(i, 9) = "100" Then
CheckBox6.Value = True ' If Month "JUNE" is Paid
Else
CheckBox6.Value = False
End If
If Sheet1.Cells(i, 10) = "100" Then
CheckBox7.Value = True ' If Month "JULY" is Paid
Else
CheckBox7.Value = False
End If
If Sheet1.Cells(i, 11) = "100" Then
CheckBox8.Value = True ' If Month "AUG" is Paid
Else
CheckBox8.Value = False
End If
If Sheet1.Cells(i, 12) = "100" Then
CheckBox9.Value = True ' If Month "SEP" is Paid
Else
CheckBox9.Value = False
End If
If Sheet1.Cells(i, 13) = "100" Then
CheckBox10.Value = True ' If Month "OCT" is Paid
Else
CheckBox10.Value = False
End If
If Sheet1.Cells(i, 14) = "100" Then
CheckBox11.Value = True ' If Month "NOV" is Paid
Else
CheckBox11.Value = False
End If
If Sheet1.Cells(i, 15) = "100" Then
CheckBox12.Value = True ' If Month "DEC" is Paid
Else
CheckBox12.Value = False
End If

End If
Next i
End Sub

Private Sub CommandButton3_Click()

End Sub

Private Sub CommandButton4_Click()
GetNextNumber
End Sub

Private Sub CommandButton5_Click()
Dim y As Integer
For y = 1 To 12
   If Me.Controls("CheckBox" & y).Value = False Then cnt = cnt + 1     ' this is where generic names can be an advantage, change the name of the checkboes if required
Next
Me.TextBox7 = cnt
Me.TextBox8 = Me.TextBox7 * "100"

End Sub

Private Sub TextBox5_AfterUpdate()
On Error Resume Next
Me.TextBox5 = CDate(Me.TextBox5)
End Sub
Private Sub TextBox4_Change()
GetData

Dim i As Long

For i = 2 To Sheet1.Range("D100000").End(xlUp).Row
If Sheet1.Cells(i, 1) = TextBox4.Text Then
If Sheet1.Cells(i, 4) = "100" Then
CheckBox1.Value = True ' If Month "January" is Paid
CheckBox1.Enabled = False
Else
CheckBox1.Value = False
End If
If Sheet1.Cells(i, 5) = "100" Then
CheckBox2.Value = True ' If Month "Feb" is Paid
Else
CheckBox2.Value = False
End If
If Sheet1.Cells(i, 6) = "100" Then
CheckBox3.Value = True ' If Month "MAR" is Paid
Else
CheckBox3.Value = False
End If
If Sheet1.Cells(i, 7) = "100" Then
CheckBox4.Value = True ' If Month "APR" is Paid
Else
CheckBox4.Value = False
End If
If Sheet1.Cells(i, 8) = "100" Then
CheckBox5.Value = True ' If Month "MAY" is Paid
Else
CheckBox5.Value = False
End If
If Sheet1.Cells(i, 9) = "100" Then
CheckBox6.Value = True ' If Month "JUNE" is Paid
Else
CheckBox6.Value = False
End If
If Sheet1.Cells(i, 10) = "100" Then
CheckBox7.Value = True ' If Month "JULY" is Paid
Else
CheckBox7.Value = False
End If
If Sheet1.Cells(i, 11) = "100" Then
CheckBox8.Value = True ' If Month "AUG" is Paid
Else
CheckBox8.Value = False
End If
If Sheet1.Cells(i, 12) = "100" Then
CheckBox9.Value = True ' If Month "SEP" is Paid
Else
CheckBox9.Value = False
End If
If Sheet1.Cells(i, 13) = "100" Then
CheckBox10.Value = True ' If Month "OCT" is Paid
Else
CheckBox10.Value = False
End If
If Sheet1.Cells(i, 14) = "100" Then
CheckBox11.Value = True ' If Month "NOV" is Paid
Else
CheckBox11.Value = False
End If
If Sheet1.Cells(i, 15) = "100" Then
CheckBox12.Value = True ' If Month "DEC" is Paid
Else
CheckBox12.Value = False
End If

End If
Next i

and now i want is for the unpaid month i want to pay, so to pay i will select the unpaid months and i want the "How Many Months = TextBox5" to display how many months i am paying

i hope you understand my problem

thank you and sorry for my bad english

[oh-sorry]

westconn1 thank you dear

The code you gave me works fine. but the problem is i use a code when i enter customer id to check whether the customer has paid to that month if the customer has paid to the month the check boxes will be ticked this is the code i am using now.

For i = 2 To Sheet1.Range("D100000").End(xlUp).Row
If Sheet1.Cells(i, 1) = TextBox4.Text Then
If Sheet1.Cells(i, 4) = "100" Then
CheckBox1.Value = True ' If Month "January" is Paid
Else
CheckBox1.Value = False
End If
If Sheet1.Cells(i, 5) = "100" Then
CheckBox2.Value = True ' If Month "Feb" is Paid
Else
CheckBox2.Value = False
End If
If Sheet1.Cells(i, 6) = "100" Then
CheckBox3.Value = True ' If Month "MAR" is Paid
Else
CheckBox3.Value = False
End If
If Sheet1.Cells(i, 7) = "100" Then
CheckBox4.Value = True ' If Month "APR" is Paid
Else
CheckBox4.Value = False
End If
If Sheet1.Cells(i, 8) = "100" Then
CheckBox5.Value = True ' If Month "MAY" is Paid
Else
CheckBox5.Value = False
End If
If Sheet1.Cells(i, 9) = "100" Then
CheckBox6.Value = True ' If Month "JUNE" is Paid
Else
CheckBox6.Value = False
End If
If Sheet1.Cells(i, 10) = "100" Then
CheckBox7.Value = True ' If Month "JULY" is Paid
Else
CheckBox7.Value = False
End If
If Sheet1.Cells(i, 11) = "100" Then
CheckBox8.Value = True ' If Month "AUG" is Paid
Else
CheckBox8.Value = False
End If
If Sheet1.Cells(i, 12) = "100" Then
CheckBox9.Value = True ' If Month "SEP" is Paid
Else
CheckBox9.Value = False
End If
If Sheet1.Cells(i, 13) = "100" Then
CheckBox10.Value = True ' If Month "OCT" is Paid
Else
CheckBox10.Value = False
End If
If Sheet1.Cells(i, 14) = "100" Then
CheckBox11.Value = True ' If Month "NOV" is Paid
Else
CheckBox11.Value = False
End If
If Sheet1.Cells(i, 15) = "100" Then
CheckBox12.Value = True ' If Month "DEC" is Paid
Else
CheckBox12.Value = False
End If

End If
Next i

and now i want is for the unpaid month i want to pay, so to pay i will select the unpaid months and i want the "How Many Months = TextBox5" to display how many months i am paying

i hope you understand my problem

thank you and sorry for my bad english

[westconn1]

to cut down on the amount of code required you could write the above code like

set fnd = range("a:a").find(textbox4) '  find id row
if fnd is nothing then exit sub   ' if id not found then quit
for c = 1 to 12
     me.controls("checkbox" &  c).value = (textbox1 = fnd.offset(,c + 3)) 
next

to increment the number of months added to be paid you can add code to each checkbox like

Private Sub CheckBox1_Click()
textbox5 = textbox5 + CheckBox1.Value * -2 - 1
End Sub

to prevent the first code to from incrementing the textbox add textbox5 ="" at the end of the first code

both codes are untested, so test before use

[oh-sorry]

westconn1 thank you alot.

Private Sub CheckBox1_Click()
textbox5 = textbox5 + CheckBox1.Value * -2 - 1
End Sub

but the secound code is giving me Runtime Error 13

[westconn1]

the secound code is giving me Runtime Error 13

it is unable to do calculation before the textbox has a value, fix like

Private Sub CheckBox1_Click()
If TextBox1 = "" Then TextBox1 = 0
TextBox1 = TextBox1 + CheckBox1.Value * -2 - 1
End Sub

also it would probably be a good idea to lock the checkboxes that are already paid so user can not uncheck them

set fnd = range("a:a").find(textbox4) '  find id row
if fnd is nothing then exit sub   ' if id not found then quit
for c = 1 to 12
    with me.controls("checkbox" &  c)
         .value = (textbox1 = fnd.offset(,c + 3))
         if .value then .locked
    end with 
next
textbox5 = 0

[oh-sorry]

westconn1 Again thank you a lot, really appreciate your help.

the calculation code works fine now.. but the second code is giving me this error

Run time Error '438
Object Doesn't Support this property or method

set fnd = range("a:a").find(textbox4) '  find id row
if fnd is nothing then exit sub   ' if id not found then quit
for c = 1 to 12
    with me.controls("checkbox" &  c)
         .value = (textbox1 = fnd.offset(,c + 3))
         if .value then .locked
    end with 
next
textbox5 = 0

[westconn1]

should have been

if .value then .locked = true

[oh-sorry]

thanks again

Private Sub CheckBox1_Click()
If TextBox1 = "" Then TextBox1 = 0
TextBox1 = TextBox1 + CheckBox1.Value * -2 - 1
End Sub

but there is a problem. when this code is in checkbox click event when i enter the Customer ID it is adding paid months also to the textbox.

example:

Customer ID = 1
customer has paid to: JAN,FEB,MAR,APR,MAY

when i enter the customer ID the checkboxes are ticked for the months and TextBox1 is adding that months also

i dont want to add if the month is paid to the textbox

[westconn1]

as posted #7 & #10, the last line will 0 the already paid months

set fnd = range("a:a").find(textbox4) '  find id row
if fnd is nothing then exit sub   ' if id not found then quit
for c = 1 to 12
    with me.controls("checkbox" &  c)
         .value = (textbox1 = fnd.offset(,c + 3))
         if .value then .locked
    end with 
next
textbox5 = 0

[oh-sorry]

oh i am sorry i fogot to add that line.. it works fine thank you.. that probelm is solved now..

now i need this code to be excuted to update the excel sheet. can you help me with this code

ID = TextBox4
House Name = TextBox2
Name = TextBox3
Fee = TextBox1 (100/- Per Month)
How Many Months = TextBox5
Amount = TextBox6

Dim i As Long
Dim x As Double

For i = 2 To Sheet1.Range("A100000").End(xlUp).Row
If Sheet1.Cells(i, 1) = TextBox4.Text Then
a = Val(Me.TextBox6) / Val(Me.TextBox1)
Me.TextBox5 = CDate(Me.TextBox5)
m = Format(Me.TextBox5, "M")
For b = m + 1 To m + a
Sheet1.Cells(i, b + 2) = Me.TextBox1.Value
Next b

End If
Next i

[westconn1]

instead of looping all the rows to find the matching ID you can just use the excel find method as post #13

m = Format(Me.TextBox5, "M")

textbox5 contains the number of months, formatting it like this seems to return some value that would not do as you expect 1 month appears to return 12, 2 or 3 months both appear to return 1, i did not test past there

i would think that the better solution would be to loop through all the checkboxes to find which months have be checked by user

for i = 1 to 12
    with me.controls("checkbox" & i)
         if not .locked and .value then      ' user checked this box, if it was filled from loading, it would be locked
             sheet1.cells(fnd).offset(,i +3).value = textbox1
         end if
    end with
next

[oh-sorry]

Thank you dear westconn1 . the code now works perfectly.. really thanks

[oh-sorry]

Now there is a Fine Criteria
for every month customer is fined like this
customer has to pay for the month before 10th of the next month
for example January month payment should be made in between 1st Jan to 10th Feb if not then customer is fined
Fine amount is 15 per month

this is ware i am stuck, can anybody help me with this

for example today is 6th April 2019

if i enter customer id = 3
this customer has not paid to jan,feb,mar

if i checked jan checkbox
i want to check weather the customer has paid to that month,
if paid do nothing
if the customer has not paid to that month then
check if customer has been fined for that month
if fined then add 15 to the textbox3(Fine)

like this if i checked Jan and feb it should do the same and add 15+15=30 to the textbox3(Fine)
because the customer is finned for 2 months

but if i checked Jan and feb, mar it should do the same and add 15+15=30 to the textbox3(Fine) but not for mar because march 10th has not yet come

[westconn1]

if i checked jan checkbox
i want to check weather the customer has paid to that month,
if paid do nothing

try like

Private Sub CheckBox1_Click()
If TextBox1 = "" Then TextBox1 = 0
TextBox1 = TextBox1 + CheckBox1.Value * -2 - 1
if date > dateserial(year(date), 2, 10) then textbox3 = textbox3 +15 
End Sub

the month number above (2) should always be 1 more than the checkbox number, ie this is the event for checkbox1, for checkbox2 the month should be 3 etc

the above does not take into account amounts overdue from the previous year, but i do not believe your form or worksheet in any way has provision for this as your checkboxes will always show from january, and your data always loads from the current year

[oh-sorry]

When i run that code its giving me this error

Run time Error '13
Type Mismatch

i tried to fix it with this code

If TextBox3 = "" Then TextBox3 = 0

but when i add that code it add the fine amount but when i uncheck checkbox 1 it also keep adding 15 to that number

[westconn1]

Private Sub CheckBox1_Click()
If TextBox1 = "" Then TextBox1 = 0
TextBox1 = TextBox1 + CheckBox1.Value * -2 - 1
if date > dateserial(year(date), 2, 10) then textbox3 = textbox3 +(CheckBox1.Value * -2 - 1) *15
End Sub

that should now remove the 15 if unchecked

maybe you should look at redesigning your form to load all unpaid fees from previous years, especially this could be required for previous year during jan, feb or mar