Order Form using Excel VBA

I’d love to post all of the inner workings of this Order Form, I’m simply not allowed.  Our manufacturing line is very competitive and I’d like to keep our advantage.  However, there are a few things I’ve built that I’d like to show off.  Here’s a simple screenshot of the INPUT page:

This allows my foreman to simply order parts by adding an X in the box.  I’m bound to the legacy database that runs the line, so instead of adding a quantity for each part, the X represents an an individual part sent to the line.  What was an interesting challenge was creating the data for the legacy database.  I had to create custom functions within VBA to handle the logic.  We were able to make most of the calculations within 2007, but my workforce uses 2003 and Office XP…  I had to make it backwards compatible.  Here’s a screenshot of what the data looks like (sorry, I had to cut some data out):

Since the database only accepts one Wire Type at a time, I had to create some macros that separate the data according to Wire Type.  When the macro is ran, a new worksheet is created for each Wire Type that is listed within the ALL FILE.  Once these are created, another macro is fired and sorts each worksheet descending except for the largest Wire Type worksheet.  Since it’s the largest worksheet, it is sorted ascending.  Weird requirement, I know… It’s what I have to work with since I’m forced to use this old database.  Once all of this is done, it’s now time to import these tabs into the database.  Another weird requirement, is that for each Wire Type, a new template database is used.  This doesn’t sound much like a database at all…  What I’ve been told is that on average, there are 3-4 Wire Types.  That means for this project, this *database* has to be copied 3 or 4 times and an import operation completed for each Wire Type.  I could be way off base here, but this doesn’t sound like a database at all, it sounds more like a band-aid.

Enough about my rants… I’m in the process of showing off the benefits of overhauling this anyway :D.  Once this is done, I’ll have to revisit my Order Form for sure.  For now though, I’m wrapping up the BETA version of this Order Form and it’s going to be tested this week. 

I’m sure you are wondering… Where are the custom functions?!?  I didn’t want to leave you hanging, so here’s a custom function that I had to write for this project.  I hope you find it useful.

Function FindWireType(rng As Range) As String
    Dim cell As Range
    Dim intDelim As Integer
    Dim intColCount As Integer
    Dim strColumn As String
    Dim strWireType As String
      
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            intDelim = InStr(2, cell.Address, "$") - 1 'Minus one to strip $ sign out. Posts back $A$ or $AA$
            intColCount = Len(Left(cell.Address, intDelim)) - 1 'Trims the left $ sign
            strColumn = Right(Left(cell.Address, intDelim), intColCount) & "8" 'Trims the cell.address to just the columns and appends an 8
            
            strWireType = ActiveWorkbook.Sheets("INPUT").Range(strColumn).Value
            
            FindWireType = strWireType
            Exit Function
        End If
    Next
End Function