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

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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 ?

VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,304
Office Version
  1. 2010
Platform
  1. Windows
got there in the end :)
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 20, 2004
Messages
3,304
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,304
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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);

VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,304
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 20, 2004
Messages
3,304
Office Version
  1. 2010
Platform
  1. Windows
ok. i will look tomorrow. now it is late here and i have to work early tomorrow
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top