Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Message box entry and cells with a formula (Excel XP)

    Here's another macro question that also relates to my invoice form in Excel.

    Message box entrys will overwrite any data that might already be in a cell. For example, I have some cells that have formula results that are also filled in by a message box. The formulas give me the most common result that I would want in that cell. When the message box comes up for that cell I either type in the same date that is already in the cell, or some other data that might occasionally be needed.

    The question is can I have a cell with a related message box left alone when the message box comes up. That way I wouldn't have to retype the exact data that is already in the cell.

    As always......thanks!
    BH Davis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message box entry and cells with a formula (Excel XP)

    Can you explain what you mean by "I have some cells that have formula results that are also filled in by a message box", and by "can I have a cell with a related message box left alone when the message box comes up"? I don't have the slightest idea what you're talking about...

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Message box entry and cells with a formula (Ex

    Sorry Hans, thought I had that explained well enough. Oh well, to try again:

    Say for example that cell A3 has a formula such as:
    =Sum(A1:A2).
    If A1 is 1 and A2 is 2 then obvously A3 will display 3.

    When I run my macro I would then have a message box come up that asks me what I want to enter in A3. It might ask "do you want to keep the formula result in A3 or change it to something else".

    If I want to keep the formula result I currently have to type a 3. I can also enter any other number I might want in the message box and that will overwrite the 3 in A3. Or I can leave the message box with no entry and A3 will end up with nothing in it when I hit <enter> to close the message box.

    Question is whether there is a way to leave the results alone in A3 even though a message box enters data into it.

    Hope that explains it a bit better. If not I can certainly put together a sample that would show what I mean.

    BH Davis

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message box entry and cells with a formula (Ex

    You can use this:

    Dim varFormula As Variant
    varFormula = Application.InputBox(Prompt:="Enter a new value or formula", _
    Default:=Range("A3").Formula, Type:=0)
    If Not varFormula = False Then
    Range("A3").Formula = varFormula
    End If

    The default will be the current formula or value, and if the user clicks Cancel or presses Esc, nothing will happen.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Message box entry and cells with a formula (Ex

    Not sure ewxactly what you are after, but another option could use a checkbox and a different cell link to never get rid of the formula

    Expanding your example:
    "Say for example that cell A3 has a formula such as:
    =Sum(A1:A2).
    "If A1 is 1 and A2 is 2 then obvously A3 will display 3.

    In B3 put the results from the message box (or input box or whatever...)

    Have a checkbox linked to (eg) cell B1, and you can change your formula in A3 to :
    =IF(B1,B3,SUM(A1:A2))

    Thus if you "check the box" the value from the message box/input box will be used, otherwise it will use the formula (SUM) in the cell

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Message box entry and cells with a formula (Ex

    Steve,

    That was one thing I considered but didn't think it all the way through. Thanks for the input.
    BH Davis

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Message box entry and cells with a formula (Ex

    Also you could even do it without the input box at all and the checkbox at all just the cell, B3 as the input.

    Your formula in A3 can be

    =IF(B3="",SUM(A1:A2),B3)

    iF the user enters anything into B3 it will use that, otherwise it will use the sum.

    Steve

Posting Permissions

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