Edit: So I haven't stopped looking up how to do this but whenever I find anything on it I get lost in how it works and how to implement it properly in my spreadsheet.
So there is a lot going on in this new userform for my inventory.
I need to create a userform with code that will allow me to search by serial number (which is typed by the user) and part type which is specified in a combobox which is already set up when the userform initializes:
Private Sub UserForm_Initialize()
'initialize the form and set the date time label
With combosearchparttype
.AddItem "Select Part Type"
.AddItem "Bill Validator"
.AddItem "CPU Tray"
.AddItem "Monitor"
.AddItem "Power Supply"
.AddItem "Printer"
.AddItem "Misc"
End With
combosearchparttype.Value = "Select Part Type"
End Sub
Display More
Which then sets the worksheet (Which we will be searching in):
Private Sub buttonsearch_Click()
Dim ws As Worksheet
'set the worksheet for data dump
If combosearchparttype = "Select Part Type" Then
MsgBox "Please Select Part Type"
Exit Sub
ElseIf combosearchparttype = "Bill Validator" Then
Set ws = Worksheets("Bill Validator")
ElseIf combosearchparttype = "CPU Tray" Then
Set ws = Worksheets("CPU Tray")
ElseIf combosearchparttype = "Monitor" Then
Set ws = Worksheets("Monitor")
ElseIf combosearchparttype = "Power Supply" Then
Set ws = Worksheets("Power Supply")
ElseIf combosearchparttype = "Printer" Then
Set ws = Worksheets("Printer")
ElseIf combosearchparttype = "Misc" Then
Set ws = Worksheets("Misc")
End If
End Sub
Display More
The next part is where the found data will be put, which is text boxes labeled as:
textresultmanufacturer
textresultmodel
txtresultserialnumber
textresultvendor
textresultlocation
We do not need a list box in this situation since we are going to be searching by a unique number that can only be associated to one part.
The last part (Which is submitting the data to a different worksheet known as "checking the part out") is something I can code relitively easily, the only part I do not know how to code is the search function.
Edit (Edit was solved)
Edit 2: This is now the final code minus the search function:
Private Sub UserForm_Initialize()
'initialize the form and set the date time label
With combosearchparttype
.AddItem "Select Part Type"
.AddItem "Bill Validator"
.AddItem "CPU Tray"
.AddItem "Monitor"
.AddItem "Power Supply"
.AddItem "Printer"
.AddItem "Misc"
End With
combosearchparttype.Value = "Select Part Type"
With comboresultparttype
.AddItem "Select Part Type"
.AddItem "Bill Validator"
.AddItem "CPU Tray"
.AddItem "Monitor"
.AddItem "Power Supply"
.AddItem "Printer"
.AddItem "Misc"
End With
comboresultparttype.Value = "Select Part Type"
With combosubmittransactiontype
.AddItem "Select Transaction Type"
.AddItem "Check Out Good"
.AddItem "Check In Bad"
End With
combosubmittransactiontype.Value = "Select Transaction Type"
labelsubmitdatetime.Caption = Now
End Sub
Private Sub combosubmittransactiontype_Change()
With Me
Select Case .combosubmittransactiontype.ListIndex
'Change To/From Label and Checked In/Out Label
Case 0
.framesubmit.Caption = "Check In/Out Information"
.labelsubmittofromlocation.Caption = "To/From Location"
.labelsubmitcheckedoutby.Caption = "Checked In/Out By"
.labelsubmitissuedescription.Visible = True
.textsubmitissuedescription.Visible = True
Case 1
.framesubmit.Caption = "Check Out Information"
.labelsubmittofromlocation.Caption = "To Location"
.labelsubmitcheckedoutby.Caption = "Checked Out By"
.labelsubmitissuedescription.Visible = False
.textsubmitissuedescription.Visible = False
Case 2
.framesubmit.Caption = "Check In Information"
.labelsubmittofromlocation.Caption = "From Location"
.labelsubmitcheckedoutby.Caption = "Checked In By"
.labelsubmitissuedescription.Visible = True
.textsubmitissuedescription.Visible = True
End Select
End With
End Sub
Private Sub comboresultparttype_Change()
With Me
Select Case .comboresultparttype.ListIndex
'Changed visibility of Misc Description
Case 0
.labelsubmitmiscdescription.Visible = True
.textsubmitmiscdescription.Visible = True
Case 1
.labelsubmitmiscdescription.Visible = False
.textsubmitmiscdescription.Visible = False
Case 2
.labelsubmitmiscdescription.Visible = False
.textsubmitmiscdescription.Visible = False
Case 3
.labelsubmitmiscdescription.Visible = False
.textsubmitmiscdescription.Visible = False
Case 4
.labelsubmitmiscdescription.Visible = False
.textsubmitmiscdescription.Visible = False
Case 5
.labelsubmitmiscdescription.Visible = False
.textsubmitmiscdescription.Visible = False
Case 6
.labelsubmitmiscdescription.Visible = True
.textsubmitmiscdescription.Visible = True
End Select
End With
End Sub
Private Sub buttonsearch_Click()
MsgBox "Search Function Coming Soon!"
Exit Sub
'Dim ws As Worksheet
'set the worksheet to search
'If combosearchparttype = "Select Part Type" Then
'MsgBox "Please Select Part Type"
'Exit Sub
'ElseIf combosearchparttype = "Bill Validator" Then
'Set ws = Worksheets("Bill Validator")
'ElseIf combosearchparttype = "CPU Tray" Then
'Set ws = Worksheets("CPU Tray")
'ElseIf combosearchparttype = "Monitor" Then
'Set ws = Worksheets("Monitor")
'ElseIf combosearchparttype = "Power Supply" Then
'Set ws = Worksheets("Power Supply")
'ElseIf combosearchparttype = "Printer" Then
'Set ws = Worksheets("Printer")
'ElseIf combosearchparttype = "Misc" Then
'Set ws = Worksheets("Misc")
'End If
End Sub
Private Sub buttonsubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
'Set spreadsheet for data dump
If combosubmittransactiontype = "Select Transaction Type" Then
MsgBox "Please Select Transaction Type"
Exit Sub
ElseIf combosubmittransactiontype = "Check Out Good" Then
Set ws = Worksheets("Outgoing Good")
ElseIf combosubmittransactiontype = "Check In Bad" Then
Set ws = Worksheets("Incoming Bad")
End If
'find first empty row
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'Part Type and Misc Description Cells
If comboresultparttype = "Select Part Type" Then
MsgBox "Please Select Part Type"
Exit Sub
ElseIf comboresultparttype = "Bill Validator" Then
ws.Cells(iRow, 1).Value = "Bill Validator"
ws.Cells(iRow, 2).Value = "N/A"
ElseIf comboresultparttype = "CPU Tray" Then
ws.Cells(iRow, 1).Value = "CPU Tray"
ws.Cells(iRow, 2).Value = "N/A"
ElseIf comboresultparttype = "Monitor" Then
ws.Cells(iRow, 1).Value = "Monitor"
ws.Cells(iRow, 2).Value = "N/A"
ElseIf comboresultparttype = "Power Supply" Then
ws.Cells(iRow, 1).Value = "Power Supply"
ws.Cells(iRow, 2).Value = "N/A"
ElseIf comboresultparttype = "Printer" Then
ws.Cells(iRow, 1).Value = "Printer"
ws.Cells(iRow, 2).Value = "N/A"
ElseIf comboresultparttype = "Misc" Then
If Me.comboresultparttype.Value = "" Then
MsgBox "Please Describe The Part"
Exit Sub
Else: ws.Cells(iRow, 2).Value = Me.comboresultparttype.Value
End If
ws.Cells(iRow, 1).Value = "Misc"
End If
'Dump the rest of the data
ws.Cells(iRow, 3).Value = Me.textresultmanufacturer.Value
ws.Cells(iRow, 4).Value = Me.textresultmodel.Value
ws.Cells(iRow, 5).Value = Me.textresultserialnumber.Value
ws.Cells(iRow, 6).Value = Me.textsubmittofromlocation.Value
'Issue Description Handling
If combosubmittransactiontype = "Check Out Good" Then
ws.Cells(iRow, 7).Value = Me.textsubmitcheckedoutby.Value
ws.Cells(iRow, 8).Value = Me.labelsubmitdatetime.Caption
ElseIf combosubmittransactiontype = "Check In Bad" Then
ws.Cells(iRow, 7).Value = Me.textsubmitissuedescription.Value
ws.Cells(iRow, 8).Value = Me.textsubmitcheckedoutby.Value
ws.Cells(iRow, 9).Value = Me.labelsubmitdatetime.Caption
End If
'Clear Data After Dump
comboresultparttype.Value = "Select Part Type"
combosubmittransactiontype.Value = "Select Transaction Type"
Me.textresultmanufacturer.Value = ""
Me.textresultmodel.Value = ""
Me.textresultserialnumber.Value = ""
Me.textsubmittofromlocation.Value = ""
Me.textsubmitissuedescription.Value = ""
Me.textsubmitcheckedoutby.Value = ""
labelsubmitdatetime.Caption = Now
End Sub
Private Sub buttoncancel_Click()
Unload Me
End Sub
Display More