Public SelectedTextBox As String
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
With ListBox1
.Clear
.ColumnCount = 16
.ColumnWidths = ""
.AddItem "Listelenen Unvan"
.List(ListBox1.ListCount - 1, 1) = "Header1"
.List(ListBox1.ListCount - 1, 2) = "Header2"
.List(ListBox1.ListCount - 1, 3) = "Header3"
.List(ListBox1.ListCount - 1, 4) = "Header4"
.List(ListBox1.ListCount - 1, 5) = "Header5"
.List(ListBox1.ListCount - 1, 6) = "Header6"
.List(ListBox1.ListCount - 1, 7) = "Header7"
End With
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, 7)
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 9)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 11)
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 15)
.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 16)
.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 18)
.List(ListBox1.ListCount - 1, 7) = sh.Cells(i, 29)
End With
End If
Next x
Next i
SelectedTextBox = "UnSor"
End Sub
Private Sub txtYönSor_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
With ListBox1
.Clear
.ColumnCount = 16
.ColumnWidths = ""
.AddItem "Admin"
.List(ListBox1.ListCount - 1, 1) = "Header1"
.List(ListBox1.ListCount - 1, 2) = "Header2"
.List(ListBox1.ListCount - 1, 3) = "Header3"
.List(ListBox1.ListCount - 1, 4) = "Header4"
.List(ListBox1.ListCount - 1, 5) = "Header5"
.List(ListBox1.ListCount - 1, 6) = "Header6"
.List(ListBox1.ListCount - 1, 7) = "Header7"
End With
For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 1))
p = Me.txtYönSor.TextLength
If Mid(sh.Cells(i, 1), x, p) = Me.txtYönSor And Me.txtYönSor <> "" 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, 7)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 11)
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 15)
.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 16)
.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 18)
.List(ListBox1.ListCount - 1, 7) = sh.Cells(i, 29)
End With
End If
Next x
Next i
SelectedTextBox = "YönSor"
End Sub
Private Sub cmdPrint_Click()
If ListBox1.ListCount = 0 Then
MsgBox "There is no data for print"""
Exit Sub
End If
Dim i As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set i = Workbooks.Add
Range("A2").Resize(ListBox1.ListCount, ListBox1.ColumnCount).Value = ListBox1.List
With i.Sheets(1)
If SelectedTextBox = "UnSor" Then
‘this widths for private sub txtUnsor_Change’
.Range("A:A").ColumnWidth = 10
.Range("B:B").ColumnWidth = 6
.Range("C:C").ColumnWidth = 22
.Range("D:D").ColumnWidth = 10
.Range("E:E").ColumnWidth = 10
.Range("F:F").ColumnWidth = 6
.Range("G:G").ColumnWidth = 5.5
.Range("H:H").ColumnWidth = 10
ElseIf SelectedTextBox = "YönSor" Then
‘This widths for pr txtYönSor_Change()
.Range("A:A").ColumnWidth = 22
.Range("B:B").ColumnWidth = 10
.Range("C:C").ColumnWidth = 6
.Range("D:D").ColumnWidth = 10
.Range("E:E").ColumnWidth = 10
.Range("F:F").ColumnWidth = 6
.Range("G:G").ColumnWidth = 5.5
.Range("H:H").ColumnWidth = 10
End If
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & " \ " & "List.pdf"
End With
i.Close False
Unload Me
' .... rest of your code
End Sub