Hello Dear MrExcel Community,
I have a listbox and textbox for search & fill. I want to print listbox after search & fill. I tried this code and "Subcript out of range run-time ‘9’ error" shows up ;
Textbox search and fill function is working clearly. They will report request from me. Which cells I will fill and print it is up to which datas they want. There are 32 column but not all column necessary.
I edited second textbox(txtUnSor_Change) for this problem and 15 column I used.
Thank you for your time and interest.
I have a listbox and textbox for search & fill. I want to print listbox after search & fill. I tried this code and "Subcript out of range run-time ‘9’ error" shows up ;
VBA Code:
Private Sub cmdYazdir_click()
Workseehts(ListBox1.Value).PrintOut
Textbox search and fill function is working clearly. They will report request from me. Which cells I will fill and print it is up to which datas they want. There are 32 column but not all column necessary.
I edited second textbox(txtUnSor_Change) for this problem and 15 column I used.
VBA Code:
Private Sub txtPlSor_Change()
Dim sh As Worksheet
ListBox1.List = Range("A2:AG750").Value
Set sh = Sheets("Worksheet")
Dim i As Long
Dim x As Long
Dim p As Long
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 33
Me.ListBox1.ColumnWidths = ""
Me.ListBox1.AddItem "Aranan Plaka"
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "Header1"
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Header2"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Header3"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "Header4"
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = "Header5"
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = "Header6"
Me.ListBox1.List(ListBox1.ListCount - 1, 7) = "Header7"
Me.ListBox1.List(ListBox1.ListCount - 1, 8) = "Header8"
Me.ListBox1.List(ListBox1.ListCount - 1, 9) = "Header9"
Me.ListBox1.List(ListBox1.ListCount - 1, 10) = "Header10"
Me.ListBox1.List(ListBox1.ListCount - 1, 11) = "Header11"
Me.ListBox1.List(ListBox1.ListCount - 1, 12) = "Header12"
Me.ListBox1.List(ListBox1.ListCount - 1, 13) = "Header13"
Me.ListBox1.List(ListBox1.ListCount - 1, 14) = "Header14"
Me.ListBox1.List(ListBox1.ListCount - 1, 15) = "Header15"
Me.ListBox1.List(ListBox1.ListCount - 1, 16) = "Header16"
Me.ListBox1.List(ListBox1.ListCount - 1, 17) = "Header17"
Me.ListBox1.List(ListBox1.ListCount - 1, 18) = "Header18"
Me.ListBox1.List(ListBox1.ListCount - 1, 19) = "Header19"
Me.ListBox1.List(ListBox1.ListCount - 1, 20) = "Header20"
Me.ListBox1.List(ListBox1.ListCount - 1, 21) = "Header21"
Me.ListBox1.List(ListBox1.ListCount - 1, 22) = "Header22"
Me.ListBox1.List(ListBox1.ListCount - 1, 23) = "Header23"
Me.ListBox1.List(ListBox1.ListCount - 1, 24) = "Header24"
Me.ListBox1.List(ListBox1.ListCount - 1, 25) = "Header25"
Me.ListBox1.List(ListBox1.ListCount - 1, 26) = "Header26"
Me.ListBox1.List(ListBox1.ListCount - 1, 27) = "Header27"
Me.ListBox1.List(ListBox1.ListCount - 1, 28) = "Header28"
Me.ListBox1.List(ListBox1.ListCount - 1, 29) = "Header29"
Me.ListBox1.List(ListBox1.ListCount - 1, 30) = "Header30"
Me.ListBox1.List(ListBox1.ListCount - 1, 31) = "Header31"
Me.ListBox1.List(ListBox1.ListCount - 1, 32) = "Header32"
For i = 2 To sh.Range("K" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 11))
p = Me.txtPlSor.TextLength
If Mid(sh.Cells(i, 11), x, p) = Me.txtPlSor And Me.txtPlSor <> "" Then
With Me.ListBox1
.AddItem sh.Cells(i, 11)
.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 1)
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 2)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 3)
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 4)
.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 5)
.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 6)
.List(ListBox1.ListCount - 1, 7) = sh.Cells(i, 7)
.List(ListBox1.ListCount - 1, 8) = sh.Cells(i, 8)
.List(ListBox1.ListCount - 1, 9) = sh.Cells(i, 9)
.List(ListBox1.ListCount - 1, 10) = sh.Cells(i, 10)
.List(ListBox1.ListCount - 1, 11) = sh.Cells(i, 11)
.List(ListBox1.ListCount - 1, 12) = sh.Cells(i, 12)
.List(ListBox1.ListCount - 1, 13) = sh.Cells(i, 13)
.List(ListBox1.ListCount - 1, 14) = sh.Cells(i, 14)
.List(ListBox1.ListCount - 1, 15) = sh.Cells(i, 15)
.List(ListBox1.ListCount - 1, 16) = sh.Cells(i, 16)
.List(ListBox1.ListCount - 1, 17) = sh.Cells(i, 17)
.List(ListBox1.ListCount - 1, 18) = sh.Cells(i, 18)
.List(ListBox1.ListCount - 1, 19) = sh.Cells(i, 19)
.List(ListBox1.ListCount - 1, 20) = sh.Cells(i, 20)
.List(ListBox1.ListCount - 1, 21) = sh.Cells(i, 21)
.List(ListBox1.ListCount - 1, 22) = sh.Cells(i, 22)
.List(ListBox1.ListCount - 1, 23) = sh.Cells(i, 23)
.List(ListBox1.ListCount - 1, 24) = sh.Cells(i, 24)
.List(ListBox1.ListCount - 1, 25) = sh.Cells(i, 25)
.List(ListBox1.ListCount - 1, 26) = sh.Cells(i, 26)
.List(ListBox1.ListCount - 1, 27) = sh.Cells(i, 27)
.List(ListBox1.ListCount - 1, 28) = sh.Cells(i, 28)
.List(ListBox1.ListCount - 1, 29) = sh.Cells(i, 29)
.List(ListBox1.ListCount - 1, 30) = sh.Cells(i, 30)
.List(ListBox1.ListCount - 1, 31) = sh.Cells(i, 31)
.List(ListBox1.ListCount - 1, 32) = sh.Cells(i, 32)
End With
End If
Next x
Next i
End Sub
Private Sub txtUnSor_Change()
Dim sh As Worksheet
ListBox1.List = Range("A2:AG750").Value
Set sh = Sheets("Worksheet")
Dim i As Long
Dim x As Long
Dim p As Long
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 16
Me.ListBox1.ColumnWidths = ""
Me.ListBox1.AddItem "Listelenen Unvan"
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "Header1"
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Header2"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Header3"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "Header4"
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = "Header5"
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = "Header6"
Me.ListBox1.List(ListBox1.ListCount - 1, 7) = "Header7"
Me.ListBox1.List(ListBox1.ListCount - 1, 8) = "Header8"
Me.ListBox1.List(ListBox1.ListCount - 1, 9) = "Header9"
Me.ListBox1.List(ListBox1.ListCount - 1, 10) = "Header10"
Me.ListBox1.List(ListBox1.ListCount - 1, 11) = "Header11"
Me.ListBox1.List(ListBox1.ListCount - 1, 12) = "Header12"
Me.ListBox1.List(ListBox1.ListCount - 1, 13) = "Header13"
Me.ListBox1.List(ListBox1.ListCount - 1, 14) = "Header14"
Me.ListBox1.List(ListBox1.ListCount - 1, 15) = "Header15"
For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 1))
p = Me.txtUnSor.TextLength
If Mid(sh.Cells(i, 1), x, p) = Me.txtUnSor And Me.txtUnSor <> "" Then
With Me.ListBox1
.AddItem sh.Cells(i, 1)
.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 1)
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 2)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 3)
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 4)
.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 5)
.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 6)
.List(ListBox1.ListCount - 1, 7) = sh.Cells(i, 7)
.List(ListBox1.ListCount - 1, 8) = sh.Cells(i, 8)
.List(ListBox1.ListCount - 1, 9) = sh.Cells(i, 9)
.List(ListBox1.ListCount - 1, 10) = sh.Cells(i, 10)
.List(ListBox1.ListCount - 1, 11) = sh.Cells(i, 12)
.List(ListBox1.ListCount - 1, 12) = sh.Cells(i, 13)
.List(ListBox1.ListCount - 1, 13) = sh.Cells(i, 16)
.List(ListBox1.ListCount - 1, 14) = sh.Cells(i, 22)
.List(ListBox1.ListCount - 1, 15) = sh.Cells(i, 26)
End With
End If
Next x
Next i
End Sub
Private Sub cmdYazdir_click()
Workseehts(ListBox1.Value).PrintOut
End Sub
Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 33
ListBox1.List = Range("A2:AG750").Value
End Sub
Thank you for your time and interest.