AW: VBA, nur ganze Zeichenfolgen suchen
08.02.2005 11:35:18
Günter
Hallo !
Hier ist das Makro:
Option Explicit
Private Sub CheckBox1_Click()
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdSearch_Click()
Dim rng As Range
Dim sFirst As String
Dim sFind As String
Dim sMod As XlLookAt
Dim n As Integer, i As Integer
Dim arrG
Dim arrL
Dim chkMod
arrG = Columns(7)
arrL = Columns(12)
Columns(12) = arrG
Columns(7) = arrL
If txtSearch = "" Then Exit Sub
ListBox1.Clear 'Listbox leeren
'Textboxen leeren
For n = 0 To 9
Controls("txt" & n + 1) = ""
Next
n = 0
'Überschriftenzeile
ListBox1.AddItem Sheets("Bestandsprotokoll").Cells(11, 1)
For n = 0 To 9
ListBox1.List(i, n) = Sheets("Bestandsprotokoll").Cells(11, n + 1)
Next
i = i + 1
sFind = txtSearch
' Suchmodus
If chkMod Then
sMod = xlWhole
Else
sMod = xlPart
End If
'If chkMod Then
'sMod = xlPart
'Else
'sMod = xlWhole
'End If
'Suchen
Set rng = Sheets("Bestandsprotokoll").Cells.Find(What:=sFind, LookIn:=xlValues, LookAt:=sMod)
If Not rng Is Nothing Then
sFirst = rng.Address
Do
ListBox1.AddItem Sheets("Bestandsprotokoll").Cells(rng.Row, 1)
For n = 0 To 9
ListBox1.List(i, n) = Sheets("Bestandsprotokoll").Cells(rng.Row, n + 1)
Next
i = i + 1
'weitersuchen bis zur ltzten Fundstelle
Set rng = Sheets("Bestandsprotokoll").Cells.FindNext(rng)
Loop While rng.Address <> sFirst
End If
If ListBox1.ListCount > 1 Then ListBox1.ListIndex = 1
arrG = Columns(7)
arrL = Columns(12)
Columns(12) = arrG
Columns(7) = arrL
End Sub
Private Sub Label14_Click()
End Sub
Private Sub Label3_Click()
End Sub
Private Sub Label11_Click()
End Sub
Private Sub ListBox1_AfterUpdate()
'Auswählen der Überschriftzeile verhindern
If ListBox1.Selected(0) = True Then ListBox1.Selected(0) = False
End Sub
Private Sub ListBox1_Change()
Dim n As Integer
If ListBox1.ListCount <= 1 Or ListBox1.ListIndex <= 0 Then Exit Sub
For n = 0 To 9
Controls("txt" & n + 1) = ListBox1.List(ListBox1.ListIndex, n)
Next
End Sub
Private Sub txtSearch_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Suche mit ENTER in der Textbox starten
If KeyCode = 13 Then cmdSearch_Click
End Sub
Private Sub UserForm_Click()
End Sub
Gruss und Danke
Günter