VBA - Using a MsgBox to suggest full packs

[Adam410]

Hi there!

I am currently putting an order form together for the business I work for.

We have different items that have different pack quantities. I want my spreadsheet to suggest this to the user. For example, mu control sheet has the following list:

ITEM	PACK QUANTITY
A	2
B	4
C	6
D	8

In the order form, it they select Item A (from a list), and a quantity that isn't a full pack quantity, I want a message box to suggest ordering a full pack.

eg if they have tried to order x11 Item A, the message box would say "Item A is in packs of 2. Would you like to change your order to 12?"

It would then be a Yes/No box: Yes would amend the order to 12, no would leave it at 11.

I've tried a few methods, none of which have worked so far!

Any help would be much appriciated!

Thanks!

 

[vletm]

Adam410
Please, reread Your writing ...
Could someone else know or do pure guess
... where to find - what has someone selected? ... and how many?
... ... please, do not answer that from the order form.
... where - in Your control sheet - is that following list?
Of course, even without those could make a code ... but ... You would need to do 'some minor modifications' to Your file.
>> a sample file which shows as same as Your original file. <<

 

[Adam410]

Attached is a VERY simplified version.

Range B2:B7 in the Order tab will be where the customer enters the quantity and selects the item (B2:B7).

The Control tab then has the pack quantity. So for example:

Customer enters ItemC, quantity 15, the message box will say "ItemC's pack quantity is 10. Do you wish to amend your order to 20?"

Then option Yes would amend the quantity, option No would leave as is.

 

[vletm]

Adam410
Okay... many times VERY ... versions
would mean a lot of extra work ... while modifying these for use.
Msg would be more useful this way ... or not?

 

[Adam410]

Perfect! Thanks very much