Option Explicit
Private Sub TextBox1_Change()
If Me.TextBox1.Value = vbNullString Then
Exit Sub
End If
Dim params As Parameters
With params
.ColumnName = "Material"
.Operator = "LIKE"
.FilterValue = Me.TextBox1.Value
End With
Call fill_combobox(params)
End Sub
Private Sub UserForm_Initialize()
Dim params As Parameters
With params
.ColumnName = "Material"
End With
Call fill_combobox(params)
End Sub
Private Sub fill_combobox(ByRef params As Parameters)
Dim rs As ADODB.Recordset
Set rs = get_entries(params)
If rs.RecordCount > 0 Then
Me.ComboBox1.Clear
Do While Not rs.EOF
Me.ComboBox1.AddItem (rs.Fields(0))
rs.MoveNext
Loop
End If
End Sub
Private Function get_entries(ByRef params As Parameters) As ADODB.Recordset
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
params.FilterValue = Replace(params.FilterValue, ";", vbNullString)
With con
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Data Source").Value = ThisWorkbook.FullName
.Properties("Extended Properties").Value = "Excel 12.0 Xml;HDR=YES"
.Open
End With
If params.Operator = vbNullString Then
strSQL = "SELECT " & params.ColumnName & " FROM [Tabelle1$];"
Else
strSQL = "SELECT " & params.ColumnName & " FROM [Tabelle1$] WHERE " & params.ColumnName & " " & params.Operator & " '" & params.FilterValue & "';"
End If
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.ActiveConnection = con
.Source = strSQL
.Open
End With
Set get_entries = rs
Set rs = Nothing
Set con = Nothing
End Function