Solved

Office supply form

Posted on 2012-03-13
4
303 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
  • 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now