Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mark
 
Posts: n/a
Default 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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 12:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"