Interactive Form

[mgriffen]

Hi, I work in a grocery store and I am trying to make a Spreadsheet file to send to my employees that will organize the information related to the products they want to put in our weekly advertisement.

I need a brand name and the number of items from that brand that will be on sale and the related UPC's for those items.
Following the brand name, I want my employee to choose from a drop-down list the number of UPC's they need to enter which will then display that number of blank subfields where they will enter the UPC's for those items before moving on to a new brand etc..

So that when they send me the completed sheet I will have an organized list of all the products and their UPC's that I need to input into our POS system.

Thank you for any help,
Matt.

 

[RobertSF]

Hi, there! Are you thinking about a drop-down that has the numbers 1, 2, 3 .... ? to indicate the number of UPCs people want to enter? There are some drawbacks to that approach. You'll wind up with a long drop-down that may not include all the numbers. What if someone wants to enter 84? Is the drop-down going to be 84 numbers long? And then you'll need a form that fits from one to N boxes, and it will never look nice. Here's a better way.

First, what you have is a one-to-many relationship. That is, for each brand, there are up to "many" UPCs (no particular limit), while each UPC belongs to one and only one brand.

It may be easier to set the brands up in a drop-down and have just one field for the UPC. Then the user selects the brand, enters the UPC, and hits Enter (or clicks OK). The form then puts this info into the spreadsheet, and returns to the user with the same brand selected and a blank field to enter the next UPC.

For this, you would have a Module plus a Form. The form would just collect the data from the user. It's the code in the module that would display the form, get the data the user entered, validate it, and put it in the spreadsheet. You could also make this run automatically by putting the module code in ThisWorkbook, under the Workbook_Open event.

In the end, you'd wind up with a spreadsheet with two columns, like this:

	Col A	Col B
Row 1	Brand	UPC
Row 2	Johnson & Johnson	143969359
Row 3	Johnson & Johnson	148604953
Row 4	Tropicana	449693024
Row 5	Kraft	948724571
Row 6	Kraft	953425834
Row 7	Kraft	926583675

<tbody>
</tbody>