ich habe in einer UserForm eine ListBox.
Ich würde gerne den markierten Eintrag in der ListBox löschen und auch gleichzeitig aus der RowSource aus der Tabelle "datenbank").
Alle Codes, die ich im Internet gefunden habe, funktionieren leider nicht.
With lbo_Datenbank
For i = .ListCount - 1 to 0 Step -1
If .Selected(i) Then Range(.RowSource).Rows(i + 1).delete shift:=xlup
Next
End With
Danach musst du ggf die RowSource nochmal neu setzen, da du ja Zeilen gelöscht hast.
With lbo_Datenbank
If .ListIndex >= 0 then Range(.RowSource).Rows(.Listindex + 1).delete shift:=xlup
End With
Gruß Daniel
If .selected(i) Then Exit for
Danach prüfst du dann i. Wenn i = -1 ist, dann wurde nichts ausgewählt. Ist i = 0 oder größer, wurde mindestens ein Wert ausgewählt.
Private Sub but_eintraglöschen_Click()
Dim Antwort As Integer
Antwort = MsgBox("Möchten Sie den ausgewählten Eintrag wirklich löschen?", vbQuestion + vbYesNo + vbDefaultButton2, "Hinweis")
If Antwort = vbYes Then
With lbo_datenbank
If .ListIndex >= 0 Then Range(.RowSource).Rows(.ListIndex + 1).Delete Shift:=xlUp
End With
Call ListBoxFüllen
Else
Exit Sub
End If
End Sub
<pre><span style="font-family=MS Sans Serif,Arial; font-size=10pt"; ><pre><span style=<span style="color:#800000"; >"font-family=MS Sans Serif,Arial; font-size=10pt"</span>; ><span style=<span style="color:#800000"; >"color:#000080"</span>; >Option</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >Explicit</span>
<b><span style=<span style="color:#800000"; >"color:#000080"</span>; >Private</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >Sub</span> ListBoxladen&#40;&#41;</b>
<span style=<span style="color:#800000"; >"color:#000080"</span>; >Dim</span> letzteZeile <span style=<span style="color:#800000"; >"color:#000080"</span>; >As</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >Long</span>
<span style=<span style="color:#800000"; >"color:#000080"</span>; >With</span> Me.lbo_datenbank
.ColumnCount = 12
.ColumnWidths = <span style=<span style="color:#800000"; >"color:#800000"</span>; >&#34;60;30;40;30;35;80;90;60;60;60;45;60&#34;</span>
.ColumnHeads = <span style=<span style="color:#800000"; >"color:#000080"</span>; >True</span>
.Font.Size = 10
<span style=<span style="color:#800000"; >"color:#000080"</span>; >End</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >With</span>
<span style=<span style="color:#800000"; >"color:#000080"</span>; >With</span> Worksheets&#40;<span style=<span style="color:#800000"; >"color:#800000"</span>; >&#34;datenbank&#34;</span>&#41;
letzteZeile = .Range&#40;<span style=<span style="color:#800000"; >"color:#800000"</span>; >&#34;A&#34;</span> &#38; .Rows.Count&#41;.End&#40;xlUp&#41;.Row
Me.lbo_datenbank.RowSource = .Name &#38; <span style=<span style="color:#800000"; >"color:#800000"</span>; >&#34;!A2&#58;L&#34;</span> &#38; letzteZeile
<span style=<span style="color:#800000"; >"color:#000080"</span>; >End</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >With</span>
<b><span style=<span style="color:#800000"; >"color:#000080"</span>; >End</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >Sub</span></b>
<b><span style=<span style="color:#800000"; >"color:#000080"</span>; >Private</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >Sub</span> but_eintragl&ouml;schen_Click&#40;&#41;</b>
<span style=<span style="color:#800000"; >"color:#000080"</span>; >Dim</span> I <span style=<span style="color:#800000"; >"color:#000080"</span>; >As</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >Integer</span>
<span style=<span style="color:#800000"; >"color:#000080"</span>; >With</span> lbo_datenbank
I = .ListIndex &#43; 2
Worksheets&#40;<span style=<span style="color:#800000"; >"color:#800000"</span>; >&#34;datenbank&#34;</span>&#41;.Rows&#40;I&#41;.Delete
<span style=<span style="color:#800000"; >"color:#000080"</span>; >End</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >With</span>
ListBoxladen
<b><span style=<span style="color:#800000"; >"color:#000080"</span>; >End</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >Sub</span></b>
<b><span style=<span style="color:#800000"; >"color:#000080"</span>; >Private</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >Sub</span> UserForm_Initialize&#40;&#41;</b>
ListBoxladen
<b><span style=<span style="color:#800000"; >"color:#000080"</span>; >End</span> <span style=<span style="color:#800000"; >"color:#000080"</span>; >Sub</span></b>
</span></pre>
<br><span style=<span style="color:#800000"; >" font-weight:bold;"</span>> Code eingef&uuml;gt mit: </span> <a style =<span style="color:#800000"; >"color:#ffff00; background-color:#000080; font-weight:bold;"</span> href=<span style="color:#800000"; >"http://www.haserodt.de/cj_pro/cj_ex.htm"</span> target=<span style="color:#800000"; >"blank"</span>>Excel Code Jeanie</a></span></pre>
<br><span style=" font-weight:bold;"> Code eingefügt mit: </span> <a style ="color:#ffff00; background-color:#000080; font-weight:bold;" href="http://www.haserodt.de/cj_pro/cj_ex.htm" target="blank">Excel Code Jeanie</a>
Gruß Uwe
Option Explicit
Private Sub ListBoxladen()
Dim letzteZeile As Long
With Me.lbo_datenbank
.ColumnCount = 12
.ColumnWidths = "60;30;40;30;35;80;90;60;60;60;45;60"
.ColumnHeads = True
.Font.Size = 10
End With
With Worksheets("datenbank")
letzteZeile = .Range("A" & .Rows.Count).End(xlUp).Row
Me.lbo_datenbank.RowSource = .Name & "!A2:L" & letzteZeile
End With
End Sub
Private Sub but_eintraglöschen_Click()
Dim I As Integer
With lbo_datenbank
I = .ListIndex + 2
Worksheets("datenbank").Rows(I).Delete
End With
ListBoxladen
End Sub
Private Sub UserForm_Initialize()
ListBoxladen
End Sub
Gruß Uwe