I have tried a few ways to use a sech box in a user form, each time i find a road block. i am only getting back in to using vba and this has stretched my knowledge,
in the example i am tiring to allow the user to type in a search term and have it retune the top results, in this case i want to use
text box 7,
to look at sheet 3
and retune all the results to list box 1
Code is below image
Thanks for all the help so far. I have been going through your forum to get this far. its a great resource. Now i am stuck i really need individual hep. Thanks again for any time you can give.
Private Sub Clear()
Dim ctl As MSForms.Control
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButtion"
ctl.Value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl
End Sub
Private Sub CommandButton2_Click()
Call Clear
End Sub
Private Sub TextBox7_Change()
Dim ws As Worksheet
Dim i As Long, j As Long, k As Long
Dim cad As String
Set ws = Sheet3
With Me.ListBox1
.Clear
.ColumnCount = 7
.ColumnWidths = "80 pt;180 pt;80 pt;80 pt;80 pt;80 pt;80 pt"
.ColumnHeads = 0
For i = 1 To ws.Range("B" & Rows.Count).End(3).Row
cad = ""
For j = 1 To Columns("O").Column
Next
If LCase(cad) Like "*" & LCase(TextBox1.Text) & "*" Then
.AddItem
.List(k, 0) = ws.Cells(i, 1)
.List(k, 1) = ws.Cells(i, 3)
.List(k, 2) = ws.Cells(i, 4)
.List(k, 3) = ws.Cells(i, 16)
.List(k, 4) = ws.Cells(i, 17)
.List(k, 5) = ws.Cells(i, 18)
.List(k, 6) = ws.Cells(i, 10)
k = k + 1
End If
Next
If .ListCount = 0 Then
MsgBox "No match"
TextBox1.SetFocus
End If
End With
End Sub
Private Sub CommandButton1_Click()
Dim emptyRow As Long
Dim screwID As Long
'Make Sheet1 active
Sheet5.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = DateTime.Now
Cells(emptyRow, 2).Value = TextBox1.Value
Cells(emptyRow, 3).Value = TextBox5.Value
Cells(emptyRow, 4).Value = TextBox6.Value
Cells(emptyRow, 5).Value = TextBox4.Value
Cells(emptyRow, 6).Value = TextBox2.Value
Cells(emptyRow, 7).Value = TextBox3.Value
Dim Output As Integer
If MsgBox("Part(s) Booked Out by " & TextBox1.Value & vbCrLf & vbCrLf & "Book out another part?", _
vbYesNo, "Booking Status") = vbYes Then
TextBox2.Value = ""
TextBox3.Value = ""
Else
Sheet1.Activate
Call Clear
Call CloseForm
End If
End Sub
Private Sub CloseForm()
Unload Me
ActiveWorkbook.Save
End Sub