Listbox Print Out Dates “########” Problem

[Tayl4n]

Dear MrExcel community,

I have a listbox. I searched and filter datas and want to print out. One column is date. Date shows true on listbox. But when I print or save as pdf file dates showing as “#########” how can I solve this ?

Private Sub cmdPrint_Click()
If ListBox1.ListCount = 0 Then
MsgBox “There is no data for print”
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim i As Workbook
Set i = Workbooks.Add
Range(“A2”).Resize(ListBox1.ListCount, ListBox1.ColumnCount).Value = ListBox1.List
Unload Me
i.Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & “\” & “List.pdf”
i.Close False

Also extra question I have 7 column for print. Can I add 1 or 2 more column and fit to A4 paper with set font size make smaller ?

 

[diddi]

got there in the end

 

[Tayl4n]

got there in the end

Yeah thank you for your interest and patient ?. But one more thing ?. I set every columns width manually for one textbox its okay. But second textbox filtering different datas and need new width values. How can I write If else for this txtbox1 and txtbox2_change() ‘s

 

[diddi]

first you need to note all the column widths you need for the first textbox, then note what you need for the second textbox.
after that, can you post the sizes and i will make the code for you

 

[Tayl4n]

first you need to note all the column widths you need for the first textbox, then note what you need for the second textbox.
after that, can you post the sizes and i will make the code for you

Private Sub cmdPrint_Click()
If ListBox1.ListCount = 0 Then
MsgBox “There is no data for print”
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim i As Workbook
Set i = Workbooks.Add
Range(“A2”).Resize(ListBox1.ListCount, ListBox1.ColumnCount).Value = ListBox1.List
Unload Me

‘this widths for private sub txtUnsor_Change’

i.Sheets(1).Range(“A:A”).ColumnWidth = 10
i.Sheets(1).Range(“B:B”).ColumnWidth = 6
i.Sheets(1).Range(“C:C”).ColumnWidth = 22
i.Sheets(1).Range(“D:D”).ColumnWidth = 10
i.Sheets(1).Range(“E:E”).ColumnWidth = 10
i.Sheets(1).Range(“F:F”).ColumnWidth = 6
i.Sheets(1).Range(“G:G”).ColumnWidth = 5.5
i.Sheets(1).Range(“H:H”).ColumnWidth = 10

‘This widths for pr txtYönSor_Change()
i.Sheets(1).Range(“A:A”).ColumnWidth = 22
i.Sheets(1).Range(“B:B”).ColumnWidth = 10
i.Sheets(1).Range(“C:C”).ColumnWidth = 6
i.Sheets(1).Range(“D:D”).ColumnWidth = 10
i.Sheets(1).Range(“E:E”).ColumnWidth = 10
i.Sheets(1).Range(“F:F”).ColumnWidth = 6
i.Sheets(1).Range(“G:G”).ColumnWidth = 5.5
i.Sheets(1).Range(“H:H”).ColumnWidth = 10


i.Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & “\” & “List.pdf”
i.Close False

 

[diddi]

i need a bit more info here. do you have code for textbox1_change and textbox2_change or do you only use 1 or the other and there is a single print button? not sure

 

[Tayl4n]

i need a bit more info here. do you have code for textbox1_change and textbox2_change or do you only use 1 or the other and there is a single print button? not sure

Codes here Im using both textboxes but not the same time. It is up to which data I want to search. I have only one print button. textbox( i mean with textbox1 = txtUnsor_change() and textbox2 = txtYönSor btw);

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"



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

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
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 16
Me.ListBox1.ColumnWidths = ""

Me.ListBox1.AddItem "Admin"

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"



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

End Sub

 

[diddi]

so when you choose which data, is one textbox hidden and the other one available to type in? how do you know which data you have chosen to view

 

[Tayl4n]

so when you choose which data, is one textbox hidden and the other one available to type in? how do you know which data you have chosen to view

No, both writable. Ex. unsor search animals and yonsor flowers.
if i write “dog” to unsor it listed dogs and if I write rose to yonsor without delete “dog” it listed roses. So listbox listed last entered textbox. Textboxes does not have any connection with each other. So last written textbox listing to listbox.

 

[diddi]

ok. i will look tomorrow. now it is late here and i have to work early tomorrow

 

[Tayl4n]

ok. i will look tomorrow. now it is late here and i have to work early tomorrow

Alright I’m waiting sir ??. If you need anything please let me know.