+ Reply to Thread
Results 1 to 7 of 7

Late Payment Warning Pop-Up Box

  1. #1
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Question Late Payment Warning Pop-Up Box

    Hello,
    I am wondering if it is possible to have a warning pop up box to appear when the workbook is opened if one of my customers is over 15 days past due date?

    For further clarification:
    The company usually works on payment terms of "60 days from the end of the month of delivery"

    My workbook has multiple customers tabs, in each tab has the Invoice number, Sale Date, Due Date and running (accumulative) total. When a customer pays I add in a different column; the amount they paid and the payment date which will then deduct this amount from the running total.

    What I would like is some way for the workbook to warn me or to somehow bring to my attention (error message pop up) if one of my customers has not paid by 15 days past their due date (therefore very late) - is this even possible in excel?

    I have found that conditional formatting does not get the required result and in such a big workbook seems to slow it down considerably.

    Any help would be greatly appreciated

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Late Payment Warning Pop-Up Box

    to your general question, yes it is possible but it will require VBA
    while someone could make one up for you ...you would need to know how it generally works in case you need to maintain it (which is highly likely)

    this aside....from what you have said..i don't think your spreadsheet setup is not conducive for this task

    wouldn't it be easier to just have one list of all invoices/due dates in one spreadsheet and highlight things that are pass due rather than doing it at customer level? akin to an outstanding invoices register
    should you then want to look at specific customers you could then easily just filter by customer either by pivot table or just plain old filtering

    ps when you say big workbook...how many transaction lines are you talking about 100k+?
    if so it might be time to look into investing into proper software
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Late Payment Warning Pop-Up Box

    Thanks for your response,

    I will have a look into VBA and see what the possibilities could be. - Thank you

    Yes agreed this is a dated workbook. The company has been using for over 10 years. Unfortunately each account (customer) has to be analyzed by not only myself but the salesman allocated to that customer and some are not very confident on a computer (to put it nicely) so information has to be shown easily and quickly without room for human error.

    I do think there should be an easier way of keeping track however haven't found it yet and we have looked into proper software but as the business is different to anything else and with so many SKU's we would have to have a custom system built and at the moment excel has done us proud without spending a fortune.

    Think I will have to call this one completed for the time being and have a look into VBA.
    Cheers for your ideas

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Late Payment Warning Pop-Up Box

    have dealt with my fair share of these people in my time
    the back end usually just works harder to make it easier for the front end

    if you need to appease the sales people, i rather suggest would look into putting it into a report either by pivot table/slicers (or VBA to pump out reports..much easier task than the intial request as it shouldn't require maintenance if built correctly) to manager Customer front

    back end could be keep for manage your account and make for ease of financial reporting a side benefit of this means that way they cant actually break any company critical data as well

    ill have a look around for examples tomorrow to let you see how potentially you can manage both sales reporting and debtors with the single file

    out of interest though roughly how many customers/skus and in turn monthly transactions are you actually looking at
    i'm assuming your actually selling "something" rather than services ?? otherwise i think SKU is being misused
    are you actually inventorying them or is everything Made to order(MTO)
    if MTO , how many of these are actually reoccurring or reused?

  5. #5
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Late Payment Warning Pop-Up Box

    How hard can it really be to filter?!

    Yes if you could find some examples that would be a real help. I have just taken over control of the workbook and looking at how I can improve it.

    Slight overview of the company:
    Steel Trading business - essentially buy bulk steel in multiple grades/width/gauge/length/product/colour/ etc. - we buy the material ex stock or 3/6 months ahead, when material arrives we hold in stock and sell on. however the stock situation is okay at the moment using excel. - currently just looking at the sales ledger.

    Mainly repeat customers - approx. 50 live customers on this side of the business. Amount of transactions can vary month to month and to each customer. could be 0 a month could be 20 to each customer. Also we could sell material on a forward basis, so the spreadsheet at the moment shows invoices that have already gone out, but also anticipated sales monthly to make sure we keep within credit limits and to know roughly how much money will be coming in for that month.

    Not an easy thing to explain as there are so many things to know. Please send some examples if you can find some as at worst there could be small things that could improve it.

    Many Thanks

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Late Payment Warning Pop-Up Box

    ok so its not much transactions every month
    so your file wont balloon till breaking point anytime soon
    Excel can definitely continue being used then


    here's something links which can some you some ways you can usee excel which you might not have thought of
    pivot table/slicers
    https://www.myexcelonline.com/blog/u...table-slicers/

    which you can combine with something like this invoice tracker
    https://www.vertex42.com/ExcelTempla...e-tracker.html

  7. #7
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Late Payment Warning Pop-Up Box

    Thank you for the links and your help, I will take a look at them and see if they could be incorporated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Monthly Payment Due with Late Charges
    By angie_lin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-12-2017, 05:05 AM
  2. late payment fee assistance
    By Runkle in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-14-2015, 10:32 AM
  3. Late payment fees
    By Runkle in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2015, 05:04 PM
  4. late payment fees
    By Runkle in forum Excel General
    Replies: 1
    Last Post: 07-07-2015, 08:41 AM
  5. Late payment penalties (Calculation)
    By tric_maq in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-08-2013, 02:30 PM
  6. Sum Late Payment amount
    By hummingbird in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2011, 05:36 PM
  7. [SOLVED] how do i locate a template for late payment fee
    By Skinner Home Center in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-10-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1