Re: suche von textstücken in spalten
12.02.2003 20:29:50
Josef
Hallo!Folgenden Text in eine Userform, entsprechende Button anlegen und alles sollte funktionieren.
Schönen abend noch
Josef
Private Sub CommandButtonSuche_Click()
Dim strCheck As String
Dim intI As Integer
Dim intJ As Integer
Dim intCount As Integer
Dim varCheck As Variant
With frmInTextBox
.CommandButtonNext.Enabled = False
.CommandButtonPrevious.Enabled = False
.Repaint
End With
' Type 2 laesst Zahlen und Strings zu.
'strSuche = CStr(Application.InputBox(prompt:="Suche Eintrag...", Type:=2))
varCheck = CStr(Application.InputBox(prompt:="Suche Eintrag...", Type:=2))
'If Len(strSuche) = 0 Then
'If varCheck Then
If Len(varCheck) > 0 And varCheck <> "Falsch" Then
strSuche = CStr(varCheck)
Else
MsgBox "Kein Suchwert eingegeben", vbExclamation
Exit Sub
End If
intJ = frmInTextBox.cboListe1.ListCount
intCount = 0
ReDim strFound(0)
For intI = 0 To intJ - 1
With frmInTextBox
cboListe1.ListIndex = intI
strCheck = cboListe1.Value
If InStr(1, LCase(strCheck), LCase(strSuche)) > 0 Then
ReDim Preserve strFound(intCount)
strFound(intCount) = strCheck
intCount = intCount + 1
End If
End With
Next intI
If intCount = 0 Then
frmInTextBox.cboListe1.ListIndex = 0
MsgBox "Kein passender Eintrag gefunden", vbExclamation
Exit Sub
End If
If UBound(strFound()) >= 0 Then
With frmInTextBox
.CommandButtonNext.Enabled = True
.CommandButtonPrevious.Enabled = False
.cboListe1.Value = strFound(0)
End With
End If
intSelected = 0
End Sub
Private Sub CommandButtonNext_Click()
Dim intNosFound As Integer
intNosFound = UBound(strFound())
strSelected = frmInTextBox.cboListe1.Value
If intSelected < intNosFound Then
Do While strSelected = strFound(intSelected)
intSelected = intSelected + 1
Loop
With frmInTextBox
cboListe1.Value = strFound(intSelected)
End With
End If
frmInTextBox.CommandButtonPrevious.Enabled = True
If intSelected >= intNosFound Then
frmInTextBox.CommandButtonNext.Enabled = False
frmInTextBox.CommandButtonPrevious.SetFocus
End If
End Sub
Private Sub CommandButtonPrevious_Click()
Dim intNosFound As Integer
intNosFound = UBound(strFound())
strSelected = frmInTextBox.cboListe1.Value
If intSelected > 0 Then
Do While strSelected = strFound(intSelected)
intSelected = intSelected - 1
Loop
With frmInTextBox
cboListe1.Value = strFound(intSelected)
End With
End If
frmInTextBox.CommandButtonNext.Enabled = True
If intSelected <= 0 Then
frmInTextBox.CommandButtonPrevious.Enabled = False
frmInTextBox.CommandButtonNext.SetFocus
End If
End Sub