Solved

Message box loop

Posted on 2012-03-26
14
640 Views
Last Modified: 2012-03-26
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
Comment
Question by:LZ1
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37767537
You have attached no file.

Dave
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37767542
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
 
LVL 30

Author Comment

by:LZ1
ID: 37767551
Sorry.  I thought I had the file attached.  The file is attached now.
ExcelSheet1.xlsm
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37767595
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
 
LVL 12

Expert Comment

by:kgerb
ID: 37767641
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
 
LVL 41

Expert Comment

by:dlmille
ID: 37767655
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
 
LVL 45

Expert Comment

by:aikimark
ID: 37767658
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

Expert Comment

by:dlmille
ID: 37767662
@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
 
LVL 12

Expert Comment

by:kgerb
ID: 37767696
@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
 
LVL 41

Expert Comment

by:dlmille
ID: 37767727
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
 
LVL 12

Expert Comment

by:kgerb
ID: 37767754
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
 
LVL 30

Author Comment

by:LZ1
ID: 37767813
Thanks everyone.  I'll check back in just a few.  I have to put out a fire first.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37768078
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
 
LVL 30

Author Closing Comment

by:LZ1
ID: 37769569
Perfect! Thanks so much
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now