Solved

Message box loop

Posted on 2012-03-26
14
633 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 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

22 Experts available now in Live!

Get 1:1 Help Now