using VBA to code EXCEL form

[dab1477]

I've created a form where I wish the user to select machine # and Part # from drop down menus in 2 separate text boxes. Once the user selects these 2 parameters, i want to look up the 2 parameters from an excel sheet based upon the MAX or most recent date that the part ran on that specific machine. I then want to extract the other variables from that same row of data INTO additional text fields that i have setup within the form. It looks something like this:

Machine# Part#
____________________
Most recent date
Variant 1 variant 2 variant 3 etc....

User selects from dropdown in Machine#, then Part #. The data would return on the form for all the variants below the line.

Data is setup in columns in a spreadsheet. My form successfully shows the drop down menus within the combo boxes on the form. I need help to populate the data requested below the line noted above.

I think I need to do an index/match, but am unsure how to do this. Although I'd like the return to come straight from the datasheet, I can imagine that the indexed/matched data is copied to a supplemental sheet within the workbook, then the form reads from the corresponding cells within the supplemental sheet. I'm not sure how to get this started. Any I asking for too much from Excel? I prefer this to be VBA. I am a VBA neophite. Thanks in advance for any direction you can provide.

 

[Gavona]

If you had a helper column that combined Machine# and Part# then that would help.
=Machine#&"_"&Part#

Match or one of the lookups would return the first occurrence in the list. So if you sorted by Date Descending would find the latest.

(From what you have posted I don't see why you need a Userform. All you have posted so far could be done with more easy validation of any user entries on a worksheet.)

Regards,

Gavin

 

[RonRepp]

Hi, Dab:

Gavin is correct. When you're using VBA in Excel, it's easiest just to setup a sheet that has all of your variants on it. Then your dropdowns just pick up those variants.

I've done many Excel applications this way, but let me just say how much easier it is to store your variants in a true database: SQL Server/Access/Oracle/MySQL.

Excel is very expensive in the RAM and Processor needed once an application becomes very large, and unless you're using newer versions of Excel, you are very limited in the # of records.

I hope this helps...

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net