Which controls would be best to use for this Userform?

I have attached a picture of a “Spoilage Percentage Table” my company uses to determine the amount overage needed to account for spoilage when ordering printing paper. For example, if we have a job where the “Function” will be ‘Spiral Bind,’ and the customer wants 15,000 pieces, looking at the table – we would multiply 15,000 by the 3.5% and then add the extra 525 sheets of paper to the original order of 15,000 sheets.

(15,000x15%) + 15,000 = 15,525

My question: Would it be logical to create a userform with just a ‘Drop Down List’ showing the functions (as listed in the table) and a ‘Scroll Bar’ with a ‘#1Text Box’ - where when I select an item from the drop down list and move the scroll bar to let’s say 15,000…another ‘#2Text Box’ shows the final spoilage count needed (in this case 15,525)?

No matter where I moved the scroll bar – whatever amount was selected, the correct percentage would automatically be selected for that function and amount of sheets and the total amount of spoilage sheets would be calculated into the ‘#2Text Box’.

Believe it or not, my original plan was to use ‘Option Buttons’ with a formula that would stretch from here to the moon until I found out Excel is limited to 7nested functions to a formula. Here is an example…

=IF(AND(B30,F21<1001),(F21*15%)+F21,IF(AND(B30,F21>1000,F21<2500),(F21*10%)+F21,IF(AND(B30,F21>2500,F21<5000),F21*7%)+F21))…

This is why I came to the conclusion VBA would be a better way to go. Any help would be appreciated, thnx.

Hi Roy, I really appreciate your wanting to help. The image was meant to be nothing more than a visual aid in what I was trying to explain. Yes, I started a workbook myself from the image shown but because of the limits imposed within nesting (as mentioned in op) I put it on the back burner. I'll attach it anyways but as you'll see...when you open it, make sure "Perfect Bind" is bulleted and then hit cell F26. You'll see the formula I began...after the third nest I finally realized what I was in for. That's when I decided perhaps VBA would be a better answer. Thnx.

You are exactly right, I am not using a Userform which is why I didn't include the workbook in the first place. I decided a Userform would be best for this type of calculation but wasn't sure which kind and the minimum amount of controls on the Userform I should use. Again. back to the OP, would a Userform with two textboxes, a slider and dropdown menu be sufficient? I have not dealt with dropdowns, or a slider on a userform.

Hi KR. Setup your in sheet form or your userform however you wish keeping in mind that you only need two inputs from your users. Function and Quantity. There is no "best way". Personally, I would just allow the user to select a function from a combobox and enter the quantity in a textbox. The slider is not typically used for such large ranges of values. It might be a bit cumbersome. Your optionbutton selections are great but less flexible than a combobox. If a function was added or removed, you would need to add or remove a control. Using a single combobox would allow for this automatically by simply updating a list. As for your logic that returns the Order Amount, see the attached...

Excellent Tom, thnx...this works great.

Because of my lack of knowledge of many of the Excel functions, "VLookup" never came to mind . I see what my new lessons will be on

One last question...For some reason when I enter anything less than 1,000 in the "Enter Quantity" cell, I get a "#NA" in the "Order Amount" cell. Would I need to make an adjustment to the VLookUp formula for any quantity less than 1,000?

The table does not account for any value under 1000. Change the first column to zero. The result will be any number from 0 to 2499 will have the percentages in the first column applied to it.