Listbox mit Überschrift
21.08.2007 21:01:00
Heinz
Habe eine Listbox zum suchen von Daten in A:E
Könnte man die Daten im Tab Blatt "Artikeln" in A1:E1 als Überschrift hernehmen ?
Könnte mir Bitte jemand helfen.
Gruß Heinz
Option Explicit
' Steuerelementer des UF
' Textbox - Name = txtSearch
' Listbox - Name = Listbox1
' CheckBox - Name = chkPart
' CheckBox - Name = chkCase
' CommandButton - Name = cmdSearch
' CommandButton - Name = cmdClose
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdSearch_Click()
Dim objSH As Worksheet
Dim rngSearch As Range
Dim strFirst As String
If txtSearch "" Then
ListBox1.Clear
Set objSH = Sheets("Artikeln")
With objSH
Set rngSearch = .Range("A:E").Find(What:=txtSearch, After:=.Range("E65536"), _
LookAt:=IIf(chkPart, xlWhole, xlPart), MatchCase:=IIf(chkCase, True, False))
If Not rngSearch Is Nothing Then
strFirst = rngSearch.Address
Do
ListBox1.AddItem .Cells(rngSearch.Row, 1)
ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(rngSearch.Row, 2)
ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(rngSearch.Row, 3)
ListBox1.List(ListBox1.ListCount - 1, 3) = .Cells(rngSearch.Row, 4)
ListBox1.List(ListBox1.ListCount - 1, 4) = .Cells(rngSearch.Row, 5)
Set rngSearch = .Range("A:E").FindNext(rngSearch)
Loop While Not rngSearch Is Nothing And rngSearch.Address strFirst
Else
ListBox1.AddItem "Kein Treffer!"
End If
End With
End If
End Sub
Private Sub txtSearch_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then cmdSearch_Click
End Sub