D3allamerican07
Board Regular
- Joined
- Jul 22, 2015
- Messages
- 101
I have a userform that allows customers to choose options from 10 comboboxes, and enter values in 10 textboxes based on their selection in the combobox.
Once the user clicks search, it needs to find the value in the combobox and filter on the textbox.value. I need help determining which value is in the combobox however.
The code I have is below.. Thanks in advance.
Once the user clicks search, it needs to find the value in the combobox and filter on the textbox.value. I need help determining which value is in the combobox however.
The code I have is below.. Thanks in advance.
Code:
Private Sub Search_Click()
Dim FErange As Range
Dim PUICrange As Range
Dim CUICrange As Range
Dim NIINrange As Range
Dim LINrange As Range
Dim SNrange As Range
Dim SLOCrange As Range
Dim DODAACrange As Range
Dim CMNrange As Range
Dim FSCrange As Range
Dim CurrentRange As Range
Set FErange = Sheets("MasterWorksheet").Range("A:A")
Set PUICrange = Sheets("MasterWorksheet").Range("C:C")
Set CUICrange = Sheets("MasterWorksheet").Range("D:D")
Set NIINrange = Sheets("MasterWorksheet").Range("F:F")
Set LINrange = Sheets("MasterWorksheet").Range("G:G")
Set SNrange = Sheets("MasterWorksheet").Range("AD:AD")
Set SLOCrange = Sheets("MasterWorksheet").Range("V:V")
Set DODAACrange = Sheets("MasterWorksheet").Range("S:S")
Set CMNrange = Sheets("MasterWorksheet").Range("AP:AP")
Set FSCrange = Sheets("MasterWorksheet").Range("J:J")
If ufCB1.Value = "Fielding Element" Then
Set CurrentRange = FErange
ElseIf ufCB2.Value = "Parent UIC" Then
Set CurrentRange = PUICrange
ElseIf ufCB3.Value = "Child UIC" Then
Set CurrentRange = CUICrange
ElseIf ufCB4.Value = "NIIN" Then
Set CurrentRange = NIINrange
ElseIf ufCB5.Value = "LIN" Then
Set CurrentRange = LINrange
ElseIf ufCB6.Value = "Serial Number" Then
Set CurrentRange = SNrange
ElseIf ufCB7.Value = "SLOC" Then
Set CurrentRange = SLOCrange
ElseIf ufCB8.Value = "DODAAC" Then
Set CurrentRange = DODAACrange
ElseIf ufCB8.Value = "Component Material Number" Then
Set CurrentRange = CMNrange
ElseIf ufCB10.Value = "FSC" Then
Set CurrentRange = FSCrange
End If
If TextBox1.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer1 = "Found"
Else
Answer1 = "Not Found"
End If
End If
If TextBox2.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox2.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer2 = "Found"
Else
Answer2 = "Not Found"
End If
End If
If TextBox3.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox3.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer3 = "Found"
Else
Answer3 = "Not Found"
End If
End If
If TextBox4.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox4.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer4 = "Found"
Else
Answer4 = "Not Found"
End If
End If
If TextBox5.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox5.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer5 = "Found"
Else
Answer5 = "Not Found"
End If
End If
If TextBox6.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox6.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer6 = "Found"
Else
Answer6 = "Not Found"
End If
End If
If TextBox7.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox7.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer7 = "Found"
Else
Answer7 = "Not Found"
End If
End If
If TextBox8.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox8.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer8 = "Found"
Else
Answer8 = "Not Found"
End If
End If
If TextBox9.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox9.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer9 = "Found"
Else
Answer9 = "Not Found"
End If
End If
If TextBox10.Value = "" Then
Else
If Not CurrentRange.Find(What:=TextBox10.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Answer10 = "Found"
Else
Answer10 = "Not Found"
End If
End If
msg = TextBox1.Value & " " & Answer1
If TextBox2 <> "" Then
msg2 = TextBox2.Value & " " & Answer2
End If
If TextBox3 <> "" Then
msg3 = TextBox3.Value & " " & Answer3
End If
If TextBox4 <> "" Then
msg4 = TextBox4.Value & " " & Answer4
End If
If TextBox5 <> "" Then
msg5 = TextBox5.Value & " " & Answer5
End If
If TextBox6 <> "" Then
msg6 = TextBox6.Value & " " & Answer6
End If
If TextBox7 <> "" Then
msg7 = TextBox7.Value & " " & Answer7
End If
If TextBox8 <> "" Then
msg8 = TextBox8.Value & " " & Answer8
End If
If TextBox9 <> "" Then
msg9 = TextBox9.Value & " " & Answer9
End If
If TextBox10 <> "" Then
msg10 = TextBox10.Value & " " & Answer10
End If
If TextBox1.Value = "" Then
MsgBox "Enter value"
Else
Dim msgValue
msgValue = MsgBox(msg & Chr(13) & msg2 & Chr(13) & msg3 & Chr(13) & msg4 & Chr(13) & msg5 & Chr(13) & msg6 & Chr(13) & msg7 & Chr(13) & msg8 & Chr(13) & msg9 & Chr(13) & msg10 & Chr(13) & Chr(13) & "Do you want to open criteria in the report", vbYesNo, "Criteria Checker")
End If
For I = 1 To 10
If Textbox(I).Value <> "" Then
End If
Next I
Dim testArray(1 To 10) As String
testArray(1) = ufCB1.Value
testArray(2) = ufCB2.Value
testArray(3) = ufCB3.Value
testArray(4) = ufCB4.Value
testArray(5) = ufCB5.Value
testArray(6) = ufCB6.Value
testArray(7) = ufCB7.Value
testArray(8) = ufCB8.Value
testArray(9) = ufCB9.Value
testArray(10) = ufCB10.Value
If msgValue = vbYes Then
Sheets("MasterWorksheet").Visible = True
Sheets("MasterWorksheet").Activate
If ActiveSheet.Cells(1, 1) <> "Main Dashboard" Or ActiveSheet.Cells(1, 3) <> "Index" Then
ActiveSheet.Rows(1).EntireRow.Insert
ActiveSheet.Cells(1, 1).Value = "Main Dashboard"
ActiveSheet.Cells(1, 1).Hyperlinks.Add Anchor:=ActiveSheet.Cells(1, 1), Address:="", SubAddress:="'" & "Dashboard" & "'" & "!A1"
ActiveSheet.Cells(1, 3).Value = "Index"
ActiveSheet.Cells(1, 3).Hyperlinks.Add Anchor:=ActiveSheet.Cells(1, 3), Address:="", SubAddress:="'" & ActiveSheet.Cells(1, 3).Value & "'" & "!A1"
ActiveSheet.Rows(1).RowHeight = 51
ActiveSheet.Rows(1).HorizontalAlignment = xlCenter
ActiveSheet.Rows(1).VerticalAlignment = xlVAlignCenter
ActiveSheet.Cells(1, 5).WrapText = True
ActiveSheet.Cells(1, 5).Font.Bold = True
ActiveSheet.Range("A1:AU1").Interior.Color = RGB(233, 255, 171)
End If
ActiveSheet.Range("A:AU").Columns.AutoFit
With ActiveSheet
ActiveSheet.AutoFilterMode = False
With .Range("A:AU")
ActiveSheet.Rows(2).Select
Selection.AutoFilter
End With
End With
If ufCB1.Value = "Fielding Element" Then
Selection.AutoFilter Field:=1, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
ElseIf ufCB1.Value = "Parent UIC" Then
Selection.AutoFilter Field:=3, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
ElseIf ufCB1.Value = "Child UIC" Then
Selection.AutoFilter Field:=4, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
ElseIf ufCB1.Value = "NIIN" Then
Selection.AutoFilter Field:=6, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
ElseIf ufCB1.Value = "LIN" Then
Selection.AutoFilter Field:=7, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
ElseIf ufCB1.Value = "Serial Number" Then
Selection.AutoFilter Field:=30, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
ElseIf ufCB1.Value = "SLOC" Then
Selection.AutoFilter Field:=21, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
ElseIf ufCB1.Value = "DODAAC" Then
Selection.AutoFilter Field:=19, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
ElseIf ufCB1.Value = "Component Material Number" Then
Selection.AutoFilter Field:=10, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
ElseIf ufCB1.Value = "FSC" Then
Selection.AutoFilter Field:=10, Criteria1:=Array( _
TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, _
TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value), _
Operator:=xlFilterValues
End If
End If
UserForm1.Hide
End Sub
Private Sub CommandButton4_Click()
Dim z As Control
For Each z In UserForm1.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
End If
Next z
Dim I As Control
For Each I In UserForm1.Controls
If TypeName(I) = "ComboBox" Then
I.Value = ""
End If
Next I
End Sub
Private Sub TextBox1_Change()
Me.TextBox2.Enabled = True
End Sub
Private Sub TextBox2_Change()
Me.TextBox3.Enabled = True
End Sub
Private Sub TextBox3_Change()
Me.TextBox4.Enabled = True
End Sub
Private Sub TextBox4_Change()
Me.TextBox5.Enabled = True
End Sub
Private Sub TextBox5_Change()
Me.TextBox6.Enabled = True
End Sub
Private Sub TextBox6_Change()
Me.TextBox7.Enabled = True
End Sub
Private Sub TextBox7_Change()
Me.TextBox8.Enabled = True
End Sub
Private Sub TextBox8_Change()
Me.TextBox9.Enabled = True
End Sub
Private Sub TextBox9_Change()
Me.TextBox10.Enabled = True
End Sub
Private Sub ufCB1_Change()
Dim z As Control
For Each z In UserForm1.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
End If
Next z
End Sub
Private Sub Textbox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
oBut2_Click
End If
End Sub