Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement / Command Button / Message Box
I have an invoice tracking sheet. I need to keep up with what customers have
not paid within 30, 60, and 90 days. I can auto filter and search for them myself, but I would like to have a command button that would check today's date in cell "L1", see if it is greater than all the dates in cells F3:F4000. If today's date is greater than any of those dates, I would like to have a message box that pops up and says "You have invoices that are 30 days past due." I am reall lost. I have not done any VB programming since college. Will someone please help me on this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement / Command Button / Message Box
I forgot to mention that there is a fomula in cells F3:F4000 that calculates
a date 30 days after the invoice was billed. I hope it can still be done. "N. McCain" wrote: I have an invoice tracking sheet. I need to keep up with what customers have not paid within 30, 60, and 90 days. I can auto filter and search for them myself, but I would like to have a command button that would check today's date in cell "L1", see if it is greater than all the dates in cells F3:F4000. If today's date is greater than any of those dates, I would like to have a message box that pops up and says "You have invoices that are 30 days past due." I am reall lost. I have not done any VB programming since college. Will someone please help me on this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement / Command Button / Message Box
Consider this alternative to what you asked for and see if it will work for
you. Select the range F3:F4000 (you can do that quickly by entering F3:F4000 into the Name Box... it is to the left of the formula bar). Once selected, click Format/Conditional Formatting in Excel's menu bar and do the following in the dialog box that appears. First, click the Add button two times so that three total conditions are showing. For each condition, select "Formula Is" in their first drop-down boxes. Now, click in the second field for Condition 1 and copy/paste this formula there... =AND(F3<"",F3<(TODAY()-90)) Now click the Format button for Condition 1 and click on the Patterns tab on the dialog box that appears. Select a color... I'd suggest the pale red one) and then click OK to get back to the Conditional Formatting dialog. Now repeat the process for the other two conditions using this formula in Condition 2... =AND(F3<"",F3<(TODAY()-60)) and this formula in Condition 3.... =AND(F3<"",F3<(TODAY()-30)) My suggestion for colors are pale blue for Condition 2 and pale yellow for Condition 3. When you are through, OK your way back to the worksheet. Any dates more than 90 days old will be highlighted in pale red; more than 60 day, but less than or equal to 90 days, in pale blue; and more than 30 days, but less than or equal to 60 days, in pale yellow. This way, you can immediately identify the status of your accounts by their color (or lack of it). Note, by the way, that I did not need to use your "today's date" in L1... Excel has the TODAY() function which generates the current date automatically. Rick "N. McCain" wrote in message ... I have an invoice tracking sheet. I need to keep up with what customers have not paid within 30, 60, and 90 days. I can auto filter and search for them myself, but I would like to have a command button that would check today's date in cell "L1", see if it is greater than all the dates in cells F3:F4000. If today's date is greater than any of those dates, I would like to have a message box that pops up and says "You have invoices that are 30 days past due." I am reall lost. I have not done any VB programming since college. Will someone please help me on this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement / Command Button / Message Box
Rick,
Thank you for the help. This is going to work great. I have one more question for you. I have a column that is called "Posted Date". After the customer pays the invoice, I put the date they paid it and then it calculates my incentive. Now, I need a "formula" that will remove the color after the invoice is paid. Thanks. "Rick Rothstein (MVP - VB)" wrote: Consider this alternative to what you asked for and see if it will work for you. Select the range F3:F4000 (you can do that quickly by entering F3:F4000 into the Name Box... it is to the left of the formula bar). Once selected, click Format/Conditional Formatting in Excel's menu bar and do the following in the dialog box that appears. First, click the Add button two times so that three total conditions are showing. For each condition, select "Formula Is" in their first drop-down boxes. Now, click in the second field for Condition 1 and copy/paste this formula there... =AND(F3<"",F3<(TODAY()-90)) Now click the Format button for Condition 1 and click on the Patterns tab on the dialog box that appears. Select a color... I'd suggest the pale red one) and then click OK to get back to the Conditional Formatting dialog. Now repeat the process for the other two conditions using this formula in Condition 2... =AND(F3<"",F3<(TODAY()-60)) and this formula in Condition 3.... =AND(F3<"",F3<(TODAY()-30)) My suggestion for colors are pale blue for Condition 2 and pale yellow for Condition 3. When you are through, OK your way back to the worksheet. Any dates more than 90 days old will be highlighted in pale red; more than 60 day, but less than or equal to 90 days, in pale blue; and more than 30 days, but less than or equal to 60 days, in pale yellow. This way, you can immediately identify the status of your accounts by their color (or lack of it). Note, by the way, that I did not need to use your "today's date" in L1... Excel has the TODAY() function which generates the current date automatically. Rick "N. McCain" wrote in message ... I have an invoice tracking sheet. I need to keep up with what customers have not paid within 30, 60, and 90 days. I can auto filter and search for them myself, but I would like to have a command button that would check today's date in cell "L1", see if it is greater than all the dates in cells F3:F4000. If today's date is greater than any of those dates, I would like to have a message box that pops up and says "You have invoices that are 30 days past due." I am reall lost. I have not done any VB programming since college. Will someone please help me on this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement / Command Button / Message Box
You didn't say which column is your "Posted Date" column, so I'll guess at
Column G (if I am wrong, just change the G to whatever is the correct column letter in the formulas below). Reselect F3:F4000, click Format/Conditional Formatting and replace the formulas I gave you earlier with these formulas... Condition 1: =AND(F3<"",F3<(TODAY()-90),G3="") Condition 2: =AND(F3<"",F3<(TODAY()-60),G3="") Condition 3: =AND(F3<"",F3<(TODAY()-30),G3="") Notice, all I did is add {,G3=""} to the existing formulas. What this does is if anything (a date, number, letter, anything) is in column G for a given row in your range, then the color will not appear in the same row in column F no matter what the date is in column F. Rick "N. McCain" wrote in message ... Rick, Thank you for the help. This is going to work great. I have one more question for you. I have a column that is called "Posted Date". After the customer pays the invoice, I put the date they paid it and then it calculates my incentive. Now, I need a "formula" that will remove the color after the invoice is paid. Thanks. "Rick Rothstein (MVP - VB)" wrote: Consider this alternative to what you asked for and see if it will work for you. Select the range F3:F4000 (you can do that quickly by entering F3:F4000 into the Name Box... it is to the left of the formula bar). Once selected, click Format/Conditional Formatting in Excel's menu bar and do the following in the dialog box that appears. First, click the Add button two times so that three total conditions are showing. For each condition, select "Formula Is" in their first drop-down boxes. Now, click in the second field for Condition 1 and copy/paste this formula there... =AND(F3<"",F3<(TODAY()-90)) Now click the Format button for Condition 1 and click on the Patterns tab on the dialog box that appears. Select a color... I'd suggest the pale red one) and then click OK to get back to the Conditional Formatting dialog. Now repeat the process for the other two conditions using this formula in Condition 2... =AND(F3<"",F3<(TODAY()-60)) and this formula in Condition 3.... =AND(F3<"",F3<(TODAY()-30)) My suggestion for colors are pale blue for Condition 2 and pale yellow for Condition 3. When you are through, OK your way back to the worksheet. Any dates more than 90 days old will be highlighted in pale red; more than 60 day, but less than or equal to 90 days, in pale blue; and more than 30 days, but less than or equal to 60 days, in pale yellow. This way, you can immediately identify the status of your accounts by their color (or lack of it). Note, by the way, that I did not need to use your "today's date" in L1... Excel has the TODAY() function which generates the current date automatically. Rick "N. McCain" wrote in message ... I have an invoice tracking sheet. I need to keep up with what customers have not paid within 30, 60, and 90 days. I can auto filter and search for them myself, but I would like to have a command button that would check today's date in cell "L1", see if it is greater than all the dates in cells F3:F4000. If today's date is greater than any of those dates, I would like to have a message box that pops up and says "You have invoices that are 30 days past due." I am reall lost. I have not done any VB programming since college. Will someone please help me on this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a warning message on a command button | New Users to Excel | |||
Pivot Table Error Message - "Command Text not set for command obje | Excel Discussion (Misc queries) | |||
What is the command to select a button in alert message? | Excel Programming | |||
Wanting to Create A Command Button Command | Excel Programming | |||
VB's Command Button vs Form's Command Button | Excel Programming |