Solved

Office supply form

Posted on 2012-03-13
4
312 Views
Last Modified: 2012-06-27
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
Comment
Question by:smidy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:reitzen
ID: 37718572
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
 

Author Comment

by:smidy
ID: 37720785
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
 
LVL 6

Accepted Solution

by:
reitzen earned 500 total points
ID: 37723066
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
 

Author Closing Comment

by:smidy
ID: 37725172
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question