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
LZ1Asked:
Who is Participating?
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.

dlmilleCommented:
You have attached no file.

Dave
0
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
Do
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

Kyle
0
LZ1Author Commented:
Sorry.  I thought I had the file attached.  The file is attached now.
ExcelSheet1.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

'Purpose:
'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

    Do
    
        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
            Loop
            
            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
            
        Loop
    
        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."
        Else
            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
        Else
            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.

Dave
ExcelSheet1.xlsm
0

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.

Kyle
0
dlmilleCommented:
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.

Dave
0
aikimarkCommented:
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.
0
dlmilleCommented:
@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.

Dave
0
kgerbChief EngineerCommented:
@Dave,
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
Do
   'All the code
Loop

Open in new window

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

Dave
0
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.

Kyle
0
LZ1Author Commented:
Thanks everyone.  I'll check back in just a few.  I have to put out a fire first.
0
aikimarkCommented:
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.
0
LZ1Author Commented:
Perfect! Thanks so much
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.