Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with Msg box
Hi
I have a worksheet made up for different cost centers with reference to invoices. For example who they are from, what date is on the invoice, what date did we receive it, amount, etc. I have written the following module and formula (see below for details) in order to bring up a message box if the amount of days between two dates are greater than 15. The reason for this is that we have a turnaround time of 14 days to pay these invoices and staff forget to issue a late letter if the invoice is paid outside the 15 days. This works great, the message box appears when the days are greater than 15 days, the only problem is when you run auto filter the message box will appear again before searching under filter. Can I add something to the module (see below) to stop the message box running again when you click ok As you are probably aware I am a novice and any help would be greatly appreciated Module Option Explicit Public Function PromptPayment() MsgBox "Please issue Prompt Payment Letter and input relevant information to PP spreadsheet" End Function Formula =IF((F4-E4)=15,PromptPayment()+F4-E4,F4-E4) Thanks Again |
#2
|
|||
|
|||
Mark,
You have found the perennial problem with MsgBox in a function , they pop-up all the time, even when you don't want them. How about a design change, rather than use a MsgBox, use Conditional Formatting. Select all of the cells with this formula, say starting in row 4, and goto CF (FormatConditional Formatting), change the condition to Formula Is, add a formula of =(F4-E4)=15, and click Format, select Pattern, and choose a colour. You will then see all items overdue in colour. Not affected by autofilter. -- HTH RP (remove nothere from the email address if mailing direct) "Mark" wrote in message ... Hi I have a worksheet made up for different cost centers with reference to invoices. For example who they are from, what date is on the invoice, what date did we receive it, amount, etc. I have written the following module and formula (see below for details) in order to bring up a message box if the amount of days between two dates are greater than 15. The reason for this is that we have a turnaround time of 14 days to pay these invoices and staff forget to issue a late letter if the invoice is paid outside the 15 days. This works great, the message box appears when the days are greater than 15 days, the only problem is when you run auto filter the message box will appear again before searching under filter. Can I add something to the module (see below) to stop the message box running again when you click ok As you are probably aware I am a novice and any help would be greatly appreciated Module Option Explicit Public Function PromptPayment() MsgBox "Please issue Prompt Payment Letter and input relevant information to PP spreadsheet" End Function Formula =IF((F4-E4)=15,PromptPayment()+F4-E4,F4-E4) Thanks Again |
#3
|
|||
|
|||
I have had Conditional Formatting running for over a year
now. Staff still forgot to issue a letter with this. that why i came up with a msg box to remind them. there must be something out the to put at the end of this VBA like ok is selected for this msg box in this cell then delete formula say???. Thanks -----Original Message----- Mark, You have found the perennial problem with MsgBox in a function , they pop-up all the time, even when you don't want them. How about a design change, rather than use a MsgBox, use Conditional Formatting. Select all of the cells with this formula, say starting in row 4, and goto CF (FormatConditional Formatting), change the condition to Formula Is, add a formula of =(F4-E4)=15, and click Format, select Pattern, and choose a colour. You will then see all items overdue in colour. Not affected by autofilter. -- HTH RP (remove nothere from the email address if mailing direct) "Mark" wrote in message ... Hi I have a worksheet made up for different cost centers with reference to invoices. For example who they are from, what date is on the invoice, what date did we receive it, amount, etc. I have written the following module and formula (see below for details) in order to bring up a message box if the amount of days between two dates are greater than 15. The reason for this is that we have a turnaround time of 14 days to pay these invoices and staff forget to issue a late letter if the invoice is paid outside the 15 days. This works great, the message box appears when the days are greater than 15 days, the only problem is when you run auto filter the message box will appear again before searching under filter. Can I add something to the module (see below) to stop the message box running again when you click ok As you are probably aware I am a novice and any help would be greatly appreciated Module Option Explicit Public Function PromptPayment() MsgBox "Please issue Prompt Payment Letter and input relevant information to PP spreadsheet" End Function Formula =IF((F4-E4)=15,PromptPayment()+F4-E4,F4-E4) Thanks Again . |
#4
|
|||
|
|||
If they ignore CF, what will stop them ignoring a MsgBox, especially if it
fires whenever the sheet recalculates/ -- HTH RP (remove nothere from the email address if mailing direct) wrote in message ... I have had Conditional Formatting running for over a year now. Staff still forgot to issue a letter with this. that why i came up with a msg box to remind them. there must be something out the to put at the end of this VBA like ok is selected for this msg box in this cell then delete formula say???. Thanks -----Original Message----- Mark, You have found the perennial problem with MsgBox in a function , they pop-up all the time, even when you don't want them. How about a design change, rather than use a MsgBox, use Conditional Formatting. Select all of the cells with this formula, say starting in row 4, and goto CF (FormatConditional Formatting), change the condition to Formula Is, add a formula of =(F4-E4)=15, and click Format, select Pattern, and choose a colour. You will then see all items overdue in colour. Not affected by autofilter. -- HTH RP (remove nothere from the email address if mailing direct) "Mark" wrote in message ... Hi I have a worksheet made up for different cost centers with reference to invoices. For example who they are from, what date is on the invoice, what date did we receive it, amount, etc. I have written the following module and formula (see below for details) in order to bring up a message box if the amount of days between two dates are greater than 15. The reason for this is that we have a turnaround time of 14 days to pay these invoices and staff forget to issue a late letter if the invoice is paid outside the 15 days. This works great, the message box appears when the days are greater than 15 days, the only problem is when you run auto filter the message box will appear again before searching under filter. Can I add something to the module (see below) to stop the message box running again when you click ok As you are probably aware I am a novice and any help would be greatly appreciated Module Option Explicit Public Function PromptPayment() MsgBox "Please issue Prompt Payment Letter and input relevant information to PP spreadsheet" End Function Formula =IF((F4-E4)=15,PromptPayment()+F4-E4,F4-E4) Thanks Again . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|