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

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
84
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 ?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
did you try widening the column width?
VBA Code:
Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 33
ListBox1.Liste = Range(“A2:AG750”).Value

VBA Code:
‘also searh and filter textbox codes about listbox

Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 33
Me.ListBox1.ColumnHeads = False
Me.ListBox1.ColumnWidths = “”
 
Upvote 0
you need to view the new sheet and check that the formatting is correct. it may require extra tweaking in code
 
Upvote 0
VBA Code:
i.Sheets(1).Range("D:D").columnwidth = 20  'change to suit
 
Upvote 0
Any help please ? I can set listbox column widths but when I print, default widths print out ?
 
Upvote 0
VBA Code:
i.Sheets(1).Range("D:D").columnwidth = 20  'change to suit
It’s work! Actually I missed that detail;
These print codes first make a new workbook and paste listbox content. In this listbox date column is “I” but in my project it belongs to “AC” column. So I tried Range(“AC:AC”) and its not work. Then I change range as (“I:I”) and its solved thank you!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top