dcsimg
Results 1 to 20 of 20

Thread: [RESOLVED] Payment Form

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    Resolved [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

    Name:  Form.jpg
Views: 144
Size:  30.1 KB
    Name:  Excel.jpg
Views: 142
Size:  22.7 KB

    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 = ""
    Last edited by oh-sorry; Mar 16th, 2019 at 07:08 AM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    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

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    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

  6. #6

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    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

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    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

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    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
    Last edited by westconn1; Mar 22nd, 2019 at 04:40 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    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

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    Re: Payment Form

    should have been
    Code:
    if .value then .locked = true
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    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

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    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

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    Re: Payment Form

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

  17. #17

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    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

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  19. #19

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    40

    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
    Last edited by oh-sorry; Apr 6th, 2019 at 05:54 AM.

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

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


Click Here to Expand Forum to Full Width