Interactive Form

mgriffen

New Member
Joined
Jun 18, 2014
Messages
4
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RobertSF

Board Regular
Joined
Jun 20, 2017
Messages
104
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 ACol B
Row 1Brand
UPC
Row 2Johnson & Johnson143969359
Row 3Johnson & Johnson148604953
Row 4Tropicana449693024
Row 5Kraft948724571
Row 6Kraft953425834
Row 7Kraft926583675

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,827
Members
409,839
Latest member
akashsadhu
Top