+ Reply to Thread
Results 1 to 4 of 4

Help with Msg box

  1. #1
    Mark
    Guest

    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. #2
    Bob Phillips
    Guest

    Re: Help with Msg box

    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 (Format>Conditional 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Guest

    Re: Help with Msg box

    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 (Format>Conditional 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" <[email protected]> wrote in

    message
    >news:[email protected]...
    >> 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. #4
    Bob Phillips
    Guest

    Re: Help with Msg box

    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)


    <[email protected]> wrote in message
    news:[email protected]...
    > 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 (Format>Conditional 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" <[email protected]> wrote in

    > message
    > >news:[email protected]...
    > >> 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 to Thread

Thread Information

Users Browsing this Thread

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

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