Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Office supply form

Hello Experts!

I haven't asked any questions in a long while since I usually find an answer here. I'm not sure if this can be done but. . .

I  have been asked to create an Excel workbook that users would be able to order work supply materials.
As they select items in the order form, it would fill a sheet for all the materials that each person orders so there would be one order log. This would then be emailed to the admin responsible for placing the order.

I would like to start with a drop down list of:

1. Vendors
User would make a selection from a list of several.
Based upon that selection, it would move to the list of materials available from that vendor.
It would automatically move on to the next selection box . . . (selection in properties right?)
2. Material Description
Gloves, Labels, Stickers, Pens . . .
again, based upon what that selected vendor supplies
3. Color or Size
Red, Black Blue, Small, Med, Large . . .
4. Part number
5. Quantity needed

I'm sure Access would handle this better by the company licenses out each one. Everyone has Excel available to them.

I'm up to the challenge. It's just been a long time since I've created and coded a form with multiple drop down lists so I need some serious help.

Thanks,
Smidy
0
Gordon Smith
Asked:
Gordon Smith
  • 2
  • 2
1 Solution
 
reitzenCommented:
What an exciting project!  I was fortunate enough to create something like this many years ago.

I used Excel filters extensively.  I had a table that contained all of our company's vendors with region codes included (some West Coast vendors were not available in the South, etc.).  So, the primary drop-down listed available vendors based upon the selected property (apartment community).  A vendor was selected and the PRODUCT table was filtered to a predefined range to display only the products/services offered by that client.  Etcetera, etcetera.

The workbook was setup with a series of master tables (all vendors, all products for all vendors, all options for all products for all vendors, etc.).

Based upon the user's selection in the VENDOR drop-down, the PRODUCTS table was filtered.  Based upon the user's selection in the PRODUCTS drop-down, the OPTIONS table was filtered.

It's actually very simple to build and create this kind of setup, it just takes a little planning and a fairly large file (lots of data to include all vendors, products, options, etc.)

You can get by without a form if you prefer to use a worksheet as the input form.  It is much easier to go this route but you do lose some of the "wow" factor.

This is a rather large undertaking so if you need more assistance you would benefit from being a little more specific in your requests.

Good luck, and good coding!!
0
 
Gordon SmithInventory Control CoordinatorAuthor Commented:
Yes it is a big project Reitzen.

I was just trying to get some basic code for the form and go from there. There will also be a form to add new vendors, materials and part numbers.

I was thinking about the WOW factor as well. I want to put a lot of time and effort into it so it will be easy for the the end user.
0
 
reitzenCommented:
You can place your code in the form itself or you can create a new module to contain all of the form's code.  The later is the method I prefer because it keeps the form lighter (loads faster).

If you want to write a value from a form object to a worksheet (reversing will allow you to write a worksheet value to a form object)
Worksheets("FormValues").Cells(iStartRow, iStartColumn).Value = frmControl.txtPeriod01.Value

Open in new window


If you want to filter a table to another range
Range(Worksheets("UnitType").Range("A1:P50")).AdvancedFilter action:=xlFilterCopy, CriteriaRange:=Worksheets("UnitTypeWorking").Range(Worksheets("UnitTypeWorking").Range("A1:P2")), CopyToRange:=Worksheets("UnitTypeWorking").Range("A5:P5"), Unique:=False

Open in new window


You can populate a drop-down or list object a couple of ways.  You can loop over a range and add items.  The easiest method is assigning a range at the object's row source.  I use named ranges for this because the size of the range will change when a filter is applied.

When defining a dynamic named range, use a formula that uses and anchor and counts the number of rows and columns to define it's range.
=OFFSET(UnitType!$A$1,0,0,COUNTA(UnitType!$A:$A),COUNTA(UnitType!$1:$1))

Open in new window


This should get you started.
Worksheets("FormValues").Cells(iStartRow, iStartColumn).Value = frmControl.txtPeriod01.Value

Open in new window

0
 
Gordon SmithInventory Control CoordinatorAuthor Commented:
Thank you  Reitzen for taking an interest. I'll start with what you gave me and go from there.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now