Message box loop

Hi guys,
I need some help with an Excel macro.

I need to loop a message box if products are entered, until nothing is entered.  So when you open the file and click on the button, enter the first code and a quantity. After it gives you the "summary" I want it to start the process over so I don't have to keep hitting the button.

Any ideas?

I've attached the file below.
LVL 30
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You have attached no file.

kgerbChief EngineerCommented:
Hello LZ1.  There's no file attached.  I making the assumption that you actually mean InputBox instead of MsgBox.  Take a look at this.  Is it close to what you want?  If not we're going to need more explanation and another attempt at uploading the file.
Sub LoopMsgBox()
Dim s As String
s = InputBox("Input Code", "Code")
If s <> "" Then Cells(Rows.Count, 1).End(xlUp).Offset(1) = s
Loop Until s = ""
End Sub

Open in new window

LZ1Author Commented:
Sorry.  I thought I had the file attached.  The file is attached now.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

We just keep an outer loop until you enter nothing in the product code prompt
Public Sub GetDiscount()

'Author:    supercoder
'Date:      Feb 9, 2006

'Set variables

Dim blnFoundCode As Boolean, intRowCount As Integer
Dim strInputProductCode As String, strProductCode As String
Dim strMessage As String, strMessagePart2 As String
Dim lngNumUnitsPurchased As Long
Dim curPricePerUnit As Currency, curTotalCost As Currency
Dim lngMinQuantityForDiscount As Long
Dim dblDiscount As Double

        blnFoundCode = False  'Starts false product code arguement
        Do While Not blnFoundCode
            strInputProductCode = UCase(InputBox("Enter a Product Code", "Enter Product Code")) 'Display message box asking for Product code to find
            If strInputProductCode = "" Then  'If nothing is entered then exit the program
                Exit Sub
            End If
            intRowCount = 1
            Do While Range("FirstCode").Offset(intRowCount - 1, 0) <> ""   'Sets cell to search for product code entered
                strProductCode = Range("FirstCode").Offset(intRowCount - 1, 0)
                If strInputProductCode = strProductCode Then  'If product code entered is found then end "If" statement
                    blnFoundCode = True
                    Exit Do
                End If
                intRowCount = intRowCount + 1  'If product code entered is not found step to the next row to look for product code
            If Not blnFoundCode Then
                MsgBox "The product code: " & strInputProductCode & " is not in the database. Please try again." 'Message box stating that the product code entered is not found
            End If
        curPricePerUnit = Range("FirstCode").Offset(intRowCount - 1, 1)  'Sets the Unit price as the cell next to the found product code cell
        lngMinQuantityForDiscount = Range("FirstCode").Offset(intRowCount - 1, 2)
        dblDiscount = Range("FirstCode").Offset(intRowCount - 1, 3)
        strMessage = "Enter number of units of product " & strInputProductCode & " to purchase:"  'Message box asking how_
        'many units to purchase
        lngNumUnitsPurchased = InputBox(strMessage, "Purchase Quantity")
        If lngNumUnitsPurchased >= lngMinQuantityForDiscount Then 'Calculates Discounted price if quantity entered is_
        'greater than or equal to the minimum discount quantity
            curTotalCost = lngNumUnitsPurchased * curPricePerUnit * (1 - dblDiscount)
            strMessagePart2 = "Because you purchased at least " & lngMinQuantityForDiscount 'Message box displaying quantity purchased and total cost with discount
            strMessagePart2 = strMessagePart2 & " units, you " & vbCrLf
            strMessagePart2 = strMessagePart2 & "got a discount of "
            strMessagePart2 = strMessagePart2 & Format(dblDiscount, "0%") & " on each unit."
            curTotalCost = lngNumUnitsPurchased * curPricePerUnit
            strMessagePart2 = ""
        End If
        strMessage = "You purchased " & lngNumUnitsPurchased & " units of product " 'Message box displaying quantity purchased and total cost with no discount
        strMssage = strMessage & strInputProductCode & "." & vbCrLf
        strMessage = strMessage & "The total cost is " & Format(curTotalCost, "$0.00")
        strMessage = strMessage & "." & vbCrLf & strMessagePart2
        MsgBox strMessage, , "Summary" 'Titles the message box "Summary"
        If lngNumUnitsPurchased >= lngMinQuantityForDiscount Then
            Worksheets("Orders").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = strProductCode 'If product purchased qualifies for a discount record on the Orders tab in the next available row
            Worksheets("Orders").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = lngNumUnitsPurchased
            Worksheets("Orders").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = curTotalCost
            Worksheets("Orders").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "" 'Do not record orders that do not get a discount
            Worksheets("Orders").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = ""
            Worksheets("Orders").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = ""
        End If
    Loop While strInputProductCode <> vbNullString

End Sub

Open in new window

See attached.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgerbChief EngineerCommented:
Alternatively, you could add this line to the very bottom of the sub, right before "End Sub".

Call GetDiscount

In this way it will recursively call the routine until the user exits by clicking Ok with nothing entered in the InputBox.

That would work, but I'd never code it that way, lol - it would keep adding to stack space the longer the person entered data (though could take a while to crash - probably a long while) - and I have to say its not good practice - makes for potential debugging problems, etc.

If this is for the purpose of doing data entry, you might also select a range of cells, with header row, and open a form.
@aikimark - I had thought of that but never seem to remember the steps to do that.  It might be useful for you to articulate those steps as a viable option.

kgerbChief EngineerCommented:
I guess I thought recursion was a legitimate programming technique.  I'm not saying your wrong as all.  I am by no means a professional programmer.  I'm just a hack engineer who fiddles around.  Can you explain more why that might make Excel crash.

I guess a better way might be to surround the whole code with a Do...Loop without any sort of Until statement?  I don't think Until is necessary b/c code already exists to exit the routine if nothing is entered in the inputbox.

Something like this
   'All the code

Open in new window

Recursion is definitely legitimate, though it makes my skin crawl to use it to avoid a loop which IMHO should be the better practice.

kgerbChief EngineerCommented:
True, I agree with you.  A loop is much better in this case.

LZ1,  No points please.  My suggestion is basically a copy of what Dave had already posted.

LZ1Author Commented:
Thanks everyone.  I'll check back in just a few.  I have to put out a fire first.
1. Make sure you have header cells for field naming
2. Select one of the header cells
3. Click the Form menu item on the Data menu tab.
4. Type your data and press the Enter key.
LZ1Author Commented:
Perfect! Thanks so much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.