asked on

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.

dlmille

You have attached no file.

Dave

kgerb

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

LZ1

ASKER

Sorry. I thought I had the file attached. The file is attached now.
ExcelSheet1.xlsm

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kgerb

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

dlmille

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

aikimark

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.

dlmille

@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

kgerb

@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

dlmille

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

kgerb

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

LZ1

ASKER

Thanks everyone. I'll check back in just a few. I have to put out a fire first.

aikimark

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.

LZ1

ASKER

Perfect! Thanks so much