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 ?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Tayl4n

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

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,271
Office Version
  1. 2010
Platform
  1. Windows
you need to view the new sheet and check that the formatting is correct. it may require extra tweaking in code
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

you need to view the new sheet and check that the formatting is correct. it may require extra tweaking in code
How can I add only date cell column widths ?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,271
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
i.Sheets(1).Range("D:D").columnwidth = 20  'change to suit
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
i.Sheets(1).Range("D:D").columnwidth = 20  'change to suit
Where should I add this ?
 

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
Any help please ? I can set listbox column widths but when I print, default widths print out 😥
 

Tayl4n

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

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,547
Members
416,116
Latest member
Joemamasuka

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