Merhaba,
Userform içindeki textbox'da değişiklik yaptığımda listboxa veriyi süzmek istiyorum. Veri üzerine çift tıklayınca da aktif hücreye ilk sütundaki değeri, aktif hücrenin sağında hücreye de ikinci sütunda yer alan değeri yazmak istiyorum. Ancak kod hata veriyor. Destek rica ederim.
Invalid Property Value hatası
Me.ListBox1.List(ListBox1.ListCount - 1, d) = .Cells(i, c + 1).Value
Kodları aldığım site,
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell.Value = ListBox1.Value
UserForm1.Hide
TextBox1.Text = ""
End Sub
Private Sub UserForm_Initialize()
With Me
.StartUpPosition = 1
.Top = 1
.Left = 1
End With
With Me.ListBox1
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'assign 2-dim array to .List property
'to overcome default column count of 10 only!!
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.Clear
'needed to overcome default limit of 10 columns only!
.List = Sayfa2.[d3:e3].Value2 ' only column heads (i.e. 1 row) to start with
'.RemoveItem 1 ' (delete eventually if no head needed at all)
.ColumnCount = 2
.ColumnWidths = "300;50"
End With
End Sub
Private Sub TextBox1_Change()
Dim ws As Worksheet ' declare data sheet as WorkSheet
Set ws = Sayfa2 ' << define data sheet's Code(Name)
With Me.ListBox1
.Clear ' remove any prior items from listbox
.List = ws.Range("d3:e3").Value2 ' display head & provide for sufficient columns
End With
If Me.TextBox1.Text = "" Then Exit Sub ' no further display, so escape
Dim SearchText As String
SearchText = StrConv(Me.TextBox1.Text, vbProperCase)
If Me.TextBox1.Text <> SearchText Then ' avoid double call of Change event
Me.TextBox1.Text = SearchText ' display ProperCase
Exit Sub ' force 2nd call after text change
End If
With ws
Dim i As Long
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
Dim lngth As Long: lngth = Len(SearchText)
Dim x As Long
For x = 1 To 2 ' range columns
Dim found As Boolean
If Left(.Cells(i, x).Value, lngth) = SearchText Then
Me.ListBox1.AddItem .Cells(i, x).Value
Dim c As Long
For c = 1 To 2
Me.ListBox1.List(ListBox1.ListCount - 1, d) = .Cells(i, c + 1).Value
Next c
found = True ' check for 1st occurrence avoiding redundant loops
End If
If found Then
found = False
Exit For ' 1st finding suffices
End If
Next x
Next i
End With
End Sub
Userform içindeki textbox'da değişiklik yaptığımda listboxa veriyi süzmek istiyorum. Veri üzerine çift tıklayınca da aktif hücreye ilk sütundaki değeri, aktif hücrenin sağında hücreye de ikinci sütunda yer alan değeri yazmak istiyorum. Ancak kod hata veriyor. Destek rica ederim.
Invalid Property Value hatası
Me.ListBox1.List(ListBox1.ListCount - 1, d) = .Cells(i, c + 1).Value
Kodları aldığım site,
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell.Value = ListBox1.Value
UserForm1.Hide
TextBox1.Text = ""
End Sub
Private Sub UserForm_Initialize()
With Me
.StartUpPosition = 1
.Top = 1
.Left = 1
End With
With Me.ListBox1
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'assign 2-dim array to .List property
'to overcome default column count of 10 only!!
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.Clear
'needed to overcome default limit of 10 columns only!
.List = Sayfa2.[d3:e3].Value2 ' only column heads (i.e. 1 row) to start with
'.RemoveItem 1 ' (delete eventually if no head needed at all)
.ColumnCount = 2
.ColumnWidths = "300;50"
End With
End Sub
Private Sub TextBox1_Change()
Dim ws As Worksheet ' declare data sheet as WorkSheet
Set ws = Sayfa2 ' << define data sheet's Code(Name)
With Me.ListBox1
.Clear ' remove any prior items from listbox
.List = ws.Range("d3:e3").Value2 ' display head & provide for sufficient columns
End With
If Me.TextBox1.Text = "" Then Exit Sub ' no further display, so escape
Dim SearchText As String
SearchText = StrConv(Me.TextBox1.Text, vbProperCase)
If Me.TextBox1.Text <> SearchText Then ' avoid double call of Change event
Me.TextBox1.Text = SearchText ' display ProperCase
Exit Sub ' force 2nd call after text change
End If
With ws
Dim i As Long
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
Dim lngth As Long: lngth = Len(SearchText)
Dim x As Long
For x = 1 To 2 ' range columns
Dim found As Boolean
If Left(.Cells(i, x).Value, lngth) = SearchText Then
Me.ListBox1.AddItem .Cells(i, x).Value
Dim c As Long
For c = 1 To 2
Me.ListBox1.List(ListBox1.ListCount - 1, d) = .Cells(i, c + 1).Value
Next c
found = True ' check for 1st occurrence avoiding redundant loops
End If
If found Then
found = False
Exit For ' 1st finding suffices
End If
Next x
Next i
End With
End Sub
