none
I need help on VBA Code RRS feed

  • Question

  • Once I enter the date in the End date text box and I clicked the "Per day" button I have all the results I need.

    However, if I have to change the date in the End Date text box. I have to click one of the buttons "Per Hour, or Per Page, etc ..." to clear the button option Per Day and fill in all text boxes again.

     I need a line of code to solve this problem.

    The same occurs in the Payment Date text box.

     Example:

    If the Due Date text box is more than the Today Date text box, on the Over Due text BOX show "Ok"

    and If the Due Date text box is less than the Today Date text box, the Over Due text box show "the difference in day between the Today Date text box and the Due Date text box".

    If the payment date on the TEXT BOX PAYMENT DATE is fill-up it show on the Due Date text box". "Paid"

    Here is the problem:

    If I change the date on the TEXT BOX PAYMENT DATE I get no answer on the TEXTO BOX OVER DUE to fix it I have to redo all steps again.

    How can I fix it?

    https://www.dropbox.com/s/bau2z1ifkn0a7d1/ProjectSummary.xlsm?dl=0

    Sunday, July 9, 2017 12:05 AM

All replies

  • Hi,

    Since your question is VBA code related, I will move it to the following dedicated forum, there you should get more professional responses:

    https://social.msdn.microsoft.com/forums/en-us/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Ethan Hua


    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, July 10, 2017 9:50 AM
  • Hi Zecapira,

    ->I need a line of code to solve this problem

    You could handle Change event of the End Date Text box.

    It looks like.

    Private Sub txtEndDate_Change()

    Dim Final As Date

    If IsDate(txtEndDate.Text) Then

    Final = txtEndDate.Value

    If OptionButtonDay = True Then

    txtHowManyDays.Value = DateDiff("d", DateValue(txtEndDate.Value), txtInitialDate) * -1 + 1

    txtDueDate = Final + 30

    If txtDueDate.Value > txtTodayDate.Value Then txtOverDue.Value = "Ok"

    If txtDueDate.Value <= txtTodayDate.Value Then txtOverDue.Value = DateDiff("d", DateValue(txtTodayDate.Value), txtDueDate) * -1 + 1

    End If

    End If

    End Sub

    ->If I change the date on the TEXT BOX PAYMENT DATE I get no answer on the TEXTO BOX OVER DUE to fix it I have to redo all steps again.

      txtOverDue = IIf(IsDate(txtPaymentDate), "Paid", ""), the Change event changes value in TEXT BOX OVER DUE as "" when text in TEXT BOX PAYMENT DATE is not a date format. I suggest you remain the value in TEXT BOX OVER DUE when text in TEXT BOX PAYMENT DATE is not a date format.

    Here it is

      txtOverDue = IIf(IsDate(txtPaymentDate), "Paid", txtOverDue)

    Best Regards,

    Terry

    Wednesday, July 12, 2017 9:52 AM