I am trying to create an inventory system at my work as the only software that we have as Excel. Basically, we have a list of work orders, in which we enter the repair, as well as the parts used. I made a code that pulled out inserted part numbers and descriptions from individual work orders to track anything, but my boss wants me to create a system that allows us to start entering the name / part number of something and guess or fill in for us. Therefore, when a combobox appears.
It worked for me to a certain extent. Lists are populated with an inventory of parts (we have a list of EXTNERAL master files), but my problem is this:
When you click "add part" of the user form, I cannot understand how to add parts in a certain range in the Work Order. All the tutorials that I have been tracking here and here only configure it to add parts in column order. Can anyone look at my (terrible, sorry) coding and see if they can help?
Private Sub UserForm_Initialize() Dim cPart As Range Dim cNum As Range Dim ws As Workbook 'Dim ComboBox1 As Variant Application.ScreenUpdating = False Set ws = Workbooks.Open("\\Capserver\iso maintenance\CAPS MASTER PARTS & PRICE LIST 2012.xls") Windows("CAPS MASTER PARTS & PRICE LIST 2012.xls").Visible = False 'ws.Sheets("CAPS ORDER FORM").Range("Name") = Sheet1.ComboBox1 'ComboBox1.Clear For Each cPart In ws.Sheets("CAPS ORDER FORM").Range("Name") With Me.cboPart .AddItem cPart.Value End With Next cPart For Each cNum In ws.Sheets("CAPS ORDER FORM").Range("Number") With Me.cboNum .AddItem cNum.Value .List(.ListCount - 1, 1) = cNum.Offset(0, 1).Value End With Next cNum End Sub Private Sub cmdAdd_Click() Dim lRow As Range Dim lPart As Long Dim ws As Worksheet Dim something As Variant Dim box As Object Set ws = Worksheets("Sheet2") With Worksheets(1).Range("A1:a500") Set lRow = .Find(What:="", SearchOrder:=xlRows, SearchDirection:=xlNext, LookIn:=xlValues) End With 'Set lRow = Range("A1") ' If VBA.IsEmpty(lRow.Value) Then ' MsgBox ("POOP!") ' Else ' Set box = lRow.End(xlDown) ' End If 'lRow = Worksheets("Sheet2").Range("A33:A37") 'ws.Cells.Find(What:="*", SearchOrder:=xlRows, (From tutorial, always returned lRow = Nothing) ' SearchDirection:=xlPrevious, LookIn:=xlValues).Row 1 lPart = Me.cboPart.ListIndex 'check for a part number If Trim(Me.cboPart.Value) = "" Then Me.cboPart.SetFocus MsgBox "Please enter a part name or number" Exit Sub End If 'copy the data to the database 'use protect and unprotect lines, ' with your password ' if worksheet is protected With ws ' .Unprotect Password:="password" .Cells(lRow, 1).Value = Me.cboPart.Value .Cells(lRow, 2).Value = Me.cboPart.List(lPart, 1) .Cells(lRow, 3).Value = Me.cboNum.Value ' .Cells(lRow, 4).Value = Me.txtDate.Value .Cells(lRow, 5).Value = Me.txtQty.Value ' .Protect Password:="password" End With 'Combobox1.linkedcell=C4 'clear the data Me.cboPart.Value = "" Me.cboNum.Value = "" Me.txtQty.Value = "" Me.cboPart.SetFocus End Sub Private Sub cmdClose_Click() Unload Me End Sub
The goal is to click on the “Add Part” button and add several files and display them on the work order (I think the range for the parts is A33: A55 or something similar)
I would also like to know if there is a way to make BOTH the part name and part numbers depending on UserForm, depending on which one you enter? Although this is a lower priority.