VBA for Input Boxes

Hello!

I have a spreadsheet that adds retail stock to a master stock list. On the control sheet, the user inputs all the information (brand, name, colour etc.) and I have some vba code that allows the user to select multiple sizes from a drop-down list.

When the user clicks submit, I would like an input box to appear for each size asking for the unique barcode (e.g if the user selected 'S, M, L' an input box should appear reading 'Please input barcode for size S' and then the same for size M and L). When finished, all the data will be copied over to master list.

I think this may need to be done with an array, but I'm not too knowledgable on how arrays work...

Hopefully, these images should help clarify the before and after intention. I have also attached my worksheet for reference.

1. User inputs information

Screenshot 2019-04-06 at 10.22.46.png

2. Input boxes appear per size asking the user for the barcode

Screenshot 2019-04-12 at 08.48.49.png > Screenshot 2019-04-12 at 08.49.10.png etc.

3. After barcodes for all sizes are collected, data is moved to the master list

Screenshot 2019-04-06 at 10.24.00.png


I have some code already and I have left a comment where the input box vba should go;

Please Login or Register  to view this content.

I'm a vba novice so any help would be appreciated! Thank you!

Hi! Thank you for your reply.

Why an input box, you've got it on screen.

I'm not sure what you mean by this question? I could've added a barcode cell to the user form I already created but this would mean that the user could only enter one size at a time and would have to put in the same data over and over, which is why I'd like the user to be able to just put the data in once and then input boxes appear to enter the unique barcodes per size. Ultimately, this should save the user time.

You say you're a novice but you have this code, who helped you?

I've been able to teach myself a lot from these forums and various websites but there's a lot of trial and error involved so I wouldn't say I'm proficient at vba.

I'm grateful for the links provided but I already know how to create an input box - I'm just not sure how to include it to get the results I want. The values will be purely numerical barcodes typed in by the user (no barcode scanner used).

I'm guessing the code will need to;

1. Use a Split function to decide how many input boxes will appear (e.g. if the size cell contains 'S, M, L' then 3 input boxes will appear in total - 1 for each size)
2. Show the input boxes one after another (adapted for each size) and allow the user to add the barcodes
3. Copy the data to the master list

I'm certainly not expecting someone to just write everything for me, but any pointers in the right direction will certainly help me piece something together!

Thanks

I've figured it out! Actually a lot simpler than I thought it'd be. I'm sure my code may be a bit clumsy though so would still appreciate any tips.

Please Login or Register  to view this content.

I've reattached my workbook so you can see what it does.

Although, the only problem I have now is the values in Col. K in the Stock List aren't automatically calculating when data is added to the sheet from the code. However, they do update if I click in the cell in question and press enter. Do you know why this might be?

I also need to put an error rule in in case someone presses cancel on the input box or puts in a non-numerical entry. Would you be able to advise me on the best way to do this?

Thank you!

Originally Posted by Keebellah

I think I'm getting the idea the 'user form' which is the worksheet Control you make your selections but then when you come to sizes you may have more than one to enter and you would like to enter a barcode for each size, correct?

You're exactly right. And all the values are common except size and barcode, so they won't have different prices etc.