basaksehirli
Altın Üye
- Katılım
- 8 Mart 2019
- Mesajlar
- 105
- Excel Vers. ve Dili
- 2016 64 Bit
- Altın Üyelik Bitiş Tarihi
- 05-05-2025
Listboxtaki verileri tarihe göre sıralamak için Module aşağıdaki kodu ekledim. Userformda togglebutton oluşturdum; tarihe göre artan ve tarihe göre azalan diye.
Ancak tarihe göre sıralama yaptığımda listboxta seçtiğim satır, textboxtlara yanlış bilgi gönderiyor. Sanırım listboxdaki bilgiler değişiyor ama excel de bilgiler yeniden sıralanmadığı için olsa gerek.
Togglebutton kodlarım:
Listbox Click kodlarım:
Ancak tarihe göre sıralama yaptığımda listboxta seçtiğim satır, textboxtlara yanlış bilgi gönderiyor. Sanırım listboxdaki bilgiler değişiyor ama excel de bilgiler yeniden sıralanmadığı için olsa gerek.
Kod:
Sub SortListBox(oLb As MSForms.ListBox, sCol As Integer, sType As Integer, sDir As Integer)
On Error Resume Next
Dim vaItems As Variant
Dim i As Long, j As Long
Dim c As Integer
Dim vTemp As Variant
'Put the items in a variant array
vaItems = oLb.List
'Sort the Array Alphabetically(1)
If sType = 1 Then
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
'Sort Ascending (1)
If sDir = 1 Then
If vaItems(i, sCol) > vaItems(j, sCol) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If
'Sort Descending (2)
ElseIf sDir = 2 Then
If vaItems(i, sCol) < vaItems(j, sCol) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If
End If
Next j
Next i
'Sort the Array Numerically(2)
'(Substitute CInt with another conversion type (CLng, CDec, etc.) depending on type of numbers in the column)
ElseIf sType = 2 Then
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
'Sort Ascending (1)
If sDir = 1 Then
If CInt(vaItems(i, sCol)) > CInt(vaItems(j, sCol)) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If
'Sort Descending (2)
ElseIf sDir = 2 Then
If CInt(vaItems(i, sCol)) < CInt(vaItems(j, sCol)) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If
End If
Next j
Next i
ElseIf sType = 3 Then 'bu kısım tarih sıralam için
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
'Sort Ascending (1)
If sDir = 1 Then
If CDate(vaItems(i, sCol)) > CDate(vaItems(j, sCol)) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If
'Sort Descending (2)
ElseIf sDir = 2 Then
If CDate(vaItems(i, sCol)) < CDate(vaItems(j, sCol)) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If
End If
Next j
Next i
End If
'Set the list to the array
oLb.List = vaItems
''<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
''Sort by the 1st column in the ListBox Alphabetically in Ascending Order
'Run "SortListBox", ListBox1, 0, 1, 1
'
''Sort by the 1st column in the ListBox Alphabetically in Descending Order
'Run "SortListBox", ListBox1, 0, 1, 2
'
''Sort by the 2nd column in the ListBox Numerically in Ascending Order
'Run "SortListBox", ListBox1, 1, 2, 1
'
''Sort by the 2nd column in the ListBox Numerically in Descending Order
'Run "SortListBox", ListBox1, 1, 2, 2
End Sub
Kod:
Private Sub ToggleButton1_Click()
ToggleButton1.TextAlign = fmTextAlignCenter
ToggleButton1.Caption = " Artan Tarih"
If ToggleButton1.Value = True Then
Run "SortListBox", ListBox1, 1, 3, 1
Else
ToggleButton1.TextAlign = fmTextAlignCenter
ToggleButton1.Caption = " Azalan Tarih"
Run "SortListBox", ListBox1, 1, 3, 2
End If
End Sub
Kod:
Private Sub ListBox1_Click()
On Error Resume Next
sat = ListBox1.List(ListBox1.ListIndex, 8)
Sheets("Veri").Cells(sat, 1).Select
TextBox100.Text = Sheets("Veri").Cells(sat, "A")
ComboBox1.Text = Sheets("Veri").Cells(sat, "B")
ComboBox3.Text = Sheets("Veri").Cells(sat, "C")
TextBox2.Text = Sheets("Veri").Cells(sat, "D")
TextBox3.Text = Sheets("Veri").Cells(sat, "E")
ComboBox5.Text = Sheets("Veri").Cells(sat, "F")
TextBox5.Text = Sheets("Veri").Cells(sat, "G")
TextBox6.Text = Sheets("Veri").Cells(sat, "H")
TextBox101.Text = Sheets("Veri").Cells(sat, "I")
Son düzenleme: