 1. [RESOLVED] Payment Form

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
Code:
```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 = ""```  Reply With Quote

2. Re: Payment Form

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

Code:
```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  Reply With Quote

3. Re: Payment Form

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  Reply With Quote

4. Re: Payment Form

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  Reply With Quote

5. Re: Payment Form

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.

Code:
```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  Reply With Quote

6. Re: Payment Form

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.

Code:
```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  Reply With Quote

7. Re: Payment Form

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

Code:
```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
Code:
```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  Reply With Quote

8. Re: Payment Form

westconn1 thank you alot.

Code:
```Private Sub CheckBox1_Click()
textbox5 = textbox5 + CheckBox1.Value * -2 - 1
End Sub```
but the secound code is giving me Runtime Error 13  Reply With Quote

9. Re: Payment Form

the secound code is giving me Runtime Error 13
it is unable to do calculation before the textbox has a value, fix like

Code:
```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
Code:
```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```  Reply With Quote

10. Re: Payment Form

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

Code:
```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```  Reply With Quote

11. Re: Payment Form

should have been
Code:
`if .value then .locked = true`  Reply With Quote

12. Re: Payment Form

thanks again

Code:
```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  Reply With Quote

13. Re: Payment Form

as posted #7 & #10, the last line will 0 the already paid months
Code:
```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```  Reply With Quote

14. Re: Payment Form

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

Code:
```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```  Reply With Quote

15. Re: Payment Form

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

Code:
```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```  Reply With Quote

16. Re: Payment Form

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

17. Re: [RESOLVED] Payment Form

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  Reply With Quote

18. Re: [RESOLVED] Payment Form

if i checked jan checkbox
i want to check weather the customer has paid to that month,
if paid do nothing
try like
Code:
```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  Reply With Quote

19. Re: [RESOLVED] Payment Form

When i run that code its giving me this error

Run time Error '13
Type Mismatch

i tried to fix it with this code
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  Reply With Quote

20. Re: [RESOLVED] Payment Form

Code:
```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  Reply With Quote Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured