- Katılım
- 14 Haziran 2006
- Mesajlar
- 575
- Altın Üyelik Bitiş Tarihi
- 10.04.2023
Userform üzerine eklediğim ComboBox1 ile verilerle resim buldurmaktayım.Ancak ComBobox1 çok yavaş çalışmaktadır.Bunun hızını artırabilirmiyiz.8000 verim bulunmaktadır.
Private Sub UserForm_Initialize()
Dim sat As Long, i As Long
For i = 1 To 20
With ListView1.ColumnHeaders
.Add , , Cells(2, i), Cells(2, i).Width * 1.2
End With
Next
ComboBox6 = Format(ComboBox6, "(####) ### ## ##")
Me.Caption = Format(Now, "dd mmmm yyyy dddd hh:mm:ss")
ListView1.View = lvwReport
ListView1.FullRowSelect = True
ListView1.Gridlines = True
With Sheets("a")
sat = .Cells(65536, "A").End(xlUp).Row
If sat < 2 Then Exit Sub
For i = 2 To sat
If WorksheetFunction.CountIf(.Range("A2:A" & i), .Cells(i, "A")) = 1 Then
ComboBox1.AddItem .Cells(i, "A").Value
End If
Next i
End With
Call ComboBox1_Change
End Sub
Private Sub ComboBox1_Change()
Dim sat As Long, i As Long, sh As Worksheet, k As Range, s As Long, adr As String
Dim deg As String
Dim fullImagePath As String
fullImagePath = "C:\DelegeResim\"
If ComboBox1.Text = "" Then
deg = "*"
Else
deg = ComboBox1.Value
End If
ListView1.ListItems.Clear
With Sheets("a")
sat = .Cells(65536, "A").End(xlUp).Row
If sat < 2 Then Exit Sub
Set k = .Range("A2:A" & sat).Find(deg, , xlValues, xlWhole)
If Not k Is Nothing Then
adr = k.Address
Do
say = say + 1
ListView1.ListItems.Add , , k.Value
ListView1.ListItems(say).SubItems(1) = k.Offset(0, 1).Value
ListView1.ListItems(say).SubItems(2) = k.Offset(0, 2).Value
ListView1.ListItems(say).SubItems(3) = k.Offset(0, 3).Value
ListView1.ListItems(say).SubItems(4) = k.Offset(0, 4).Value
ListView1.ListItems(say).SubItems(5) = k.Offset(0, 5).Value
ListView1.ListItems(say).SubItems(6) = k.Offset(0, 6).Value
ListView1.ListItems(say).SubItems(7) = k.Offset(0, 7).Value
ListView1.ListItems(say).SubItems(8) = k.Offset(0, 8).Value
ListView1.ListItems(say).SubItems(9) = k.Offset(0, 9).Value
ListView1.ListItems(say).SubItems(10) = k.Offset(0, 10).Value
ListView1.ListItems(say).SubItems(11) = k.Offset(0, 11).Value
ListView1.ListItems(say).SubItems(12) = k.Offset(0, 12).Value
ListView1.ListItems(say).SubItems(13) = k.Offset(0, 13).Value
ListView1.ListItems(say).SubItems(14) = k.Offset(0, 14).Value
ListView1.ListItems(say).SubItems(15) = k.Offset(0, 15).Value
ListView1.ListItems(say).SubItems(16) = k.Offset(0, 16).Value
ListView1.ListItems(say).SubItems(17) = k.Offset(0, 17).Value
ListView1.ListItems(say).SubItems(18) = k.Offset(0, 18).Value
ListView1.ListItems(say).SubItems(19) = k.Offset(0, 19).Value
ListView1.ListItems.Item(say).Bold = True
ComboBox2.Text = ListView1.SelectedItem.Text
ComboBox3.Text = ListView1.SelectedItem.ListSubItems.Item(1).Text
ComboBox4.Text = ListView1.SelectedItem.ListSubItems.Item(2).Text
ComboBox5.Text = ListView1.SelectedItem.ListSubItems.Item(3).Text
ComboBox6.Text = ListView1.SelectedItem.ListSubItems.Item(4).Text
ComboBox7.Text = ListView1.SelectedItem.ListSubItems.Item(5).Text
ComboBox8.Text = ListView1.SelectedItem.ListSubItems.Item(6).Text
ComboBox9.Text = ListView1.SelectedItem.ListSubItems.Item(7).Text
ComboBox10.Text = ListView1.SelectedItem.ListSubItems.Item(8).Text
ComboBox11.Text = ListView1.SelectedItem.ListSubItems.Item(9).Text
ComboBox12.Text = ListView1.SelectedItem.ListSubItems.Item(10).Text
ComboBox13.Text = ListView1.SelectedItem.ListSubItems.Item(11).Text
ComboBox14.Text = ListView1.SelectedItem.ListSubItems.Item(12).Text
ComboBox15.Text = ListView1.SelectedItem.ListSubItems.Item(13).Text
ComboBox16.Text = ListView1.SelectedItem.ListSubItems.Item(14).Text
ComboBox17.Text = ListView1.SelectedItem.ListSubItems.Item(15).Text
ComboBox18.Text = ListView1.SelectedItem.ListSubItems.Item(16).Text
ComboBox19.Text = ListView1.SelectedItem.ListSubItems.Item(17).Text
ComboBox20.Text = ListView1.SelectedItem.ListSubItems.Item(19).Text
TextBox1.Text = ListView1.SelectedItem.ListSubItems.Item(18).Text
ComboBox6 = Format(ComboBox6, "(####) ### ## ##")
If say Mod 2 = 0 Then
ListView1.ListItems.Item(say).ForeColor = vbRed
Else
ListView1.ListItems.Item(say).ForeColor = vbBlue
End If
Set k = .Range("A2:A" & sat).FindNext(k)
Loop While Not k Is Nothing And k.Address <> adr
End If
End With
If ComboBox4.Text <> "" Then
fullImagePath = fullImagePath & ComboBox4.Text & ".jpg"
If Dir(fullImagePath) <> "" Then
resim.Picture = LoadPicture(fullImagePath)
End If
End If
End Sub
Private Sub UserForm_Initialize()
Dim sat As Long, i As Long
For i = 1 To 20
With ListView1.ColumnHeaders
.Add , , Cells(2, i), Cells(2, i).Width * 1.2
End With
Next
ComboBox6 = Format(ComboBox6, "(####) ### ## ##")
Me.Caption = Format(Now, "dd mmmm yyyy dddd hh:mm:ss")
ListView1.View = lvwReport
ListView1.FullRowSelect = True
ListView1.Gridlines = True
With Sheets("a")
sat = .Cells(65536, "A").End(xlUp).Row
If sat < 2 Then Exit Sub
For i = 2 To sat
If WorksheetFunction.CountIf(.Range("A2:A" & i), .Cells(i, "A")) = 1 Then
ComboBox1.AddItem .Cells(i, "A").Value
End If
Next i
End With
Call ComboBox1_Change
End Sub
Private Sub ComboBox1_Change()
Dim sat As Long, i As Long, sh As Worksheet, k As Range, s As Long, adr As String
Dim deg As String
Dim fullImagePath As String
fullImagePath = "C:\DelegeResim\"
If ComboBox1.Text = "" Then
deg = "*"
Else
deg = ComboBox1.Value
End If
ListView1.ListItems.Clear
With Sheets("a")
sat = .Cells(65536, "A").End(xlUp).Row
If sat < 2 Then Exit Sub
Set k = .Range("A2:A" & sat).Find(deg, , xlValues, xlWhole)
If Not k Is Nothing Then
adr = k.Address
Do
say = say + 1
ListView1.ListItems.Add , , k.Value
ListView1.ListItems(say).SubItems(1) = k.Offset(0, 1).Value
ListView1.ListItems(say).SubItems(2) = k.Offset(0, 2).Value
ListView1.ListItems(say).SubItems(3) = k.Offset(0, 3).Value
ListView1.ListItems(say).SubItems(4) = k.Offset(0, 4).Value
ListView1.ListItems(say).SubItems(5) = k.Offset(0, 5).Value
ListView1.ListItems(say).SubItems(6) = k.Offset(0, 6).Value
ListView1.ListItems(say).SubItems(7) = k.Offset(0, 7).Value
ListView1.ListItems(say).SubItems(8) = k.Offset(0, 8).Value
ListView1.ListItems(say).SubItems(9) = k.Offset(0, 9).Value
ListView1.ListItems(say).SubItems(10) = k.Offset(0, 10).Value
ListView1.ListItems(say).SubItems(11) = k.Offset(0, 11).Value
ListView1.ListItems(say).SubItems(12) = k.Offset(0, 12).Value
ListView1.ListItems(say).SubItems(13) = k.Offset(0, 13).Value
ListView1.ListItems(say).SubItems(14) = k.Offset(0, 14).Value
ListView1.ListItems(say).SubItems(15) = k.Offset(0, 15).Value
ListView1.ListItems(say).SubItems(16) = k.Offset(0, 16).Value
ListView1.ListItems(say).SubItems(17) = k.Offset(0, 17).Value
ListView1.ListItems(say).SubItems(18) = k.Offset(0, 18).Value
ListView1.ListItems(say).SubItems(19) = k.Offset(0, 19).Value
ListView1.ListItems.Item(say).Bold = True
ComboBox2.Text = ListView1.SelectedItem.Text
ComboBox3.Text = ListView1.SelectedItem.ListSubItems.Item(1).Text
ComboBox4.Text = ListView1.SelectedItem.ListSubItems.Item(2).Text
ComboBox5.Text = ListView1.SelectedItem.ListSubItems.Item(3).Text
ComboBox6.Text = ListView1.SelectedItem.ListSubItems.Item(4).Text
ComboBox7.Text = ListView1.SelectedItem.ListSubItems.Item(5).Text
ComboBox8.Text = ListView1.SelectedItem.ListSubItems.Item(6).Text
ComboBox9.Text = ListView1.SelectedItem.ListSubItems.Item(7).Text
ComboBox10.Text = ListView1.SelectedItem.ListSubItems.Item(8).Text
ComboBox11.Text = ListView1.SelectedItem.ListSubItems.Item(9).Text
ComboBox12.Text = ListView1.SelectedItem.ListSubItems.Item(10).Text
ComboBox13.Text = ListView1.SelectedItem.ListSubItems.Item(11).Text
ComboBox14.Text = ListView1.SelectedItem.ListSubItems.Item(12).Text
ComboBox15.Text = ListView1.SelectedItem.ListSubItems.Item(13).Text
ComboBox16.Text = ListView1.SelectedItem.ListSubItems.Item(14).Text
ComboBox17.Text = ListView1.SelectedItem.ListSubItems.Item(15).Text
ComboBox18.Text = ListView1.SelectedItem.ListSubItems.Item(16).Text
ComboBox19.Text = ListView1.SelectedItem.ListSubItems.Item(17).Text
ComboBox20.Text = ListView1.SelectedItem.ListSubItems.Item(19).Text
TextBox1.Text = ListView1.SelectedItem.ListSubItems.Item(18).Text
ComboBox6 = Format(ComboBox6, "(####) ### ## ##")
If say Mod 2 = 0 Then
ListView1.ListItems.Item(say).ForeColor = vbRed
Else
ListView1.ListItems.Item(say).ForeColor = vbBlue
End If
Set k = .Range("A2:A" & sat).FindNext(k)
Loop While Not k Is Nothing And k.Address <> adr
End If
End With
If ComboBox4.Text <> "" Then
fullImagePath = fullImagePath & ComboBox4.Text & ".jpg"
If Dir(fullImagePath) <> "" Then
resim.Picture = LoadPicture(fullImagePath)
End If
End If
End Sub