• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

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.
0
LZ1
Asked:
LZ1
  • 5
  • 4
  • 3
  • +1
1 Solution
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now