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

T

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 ?
 
Sort by date Sort by votes

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
hi. make sure all of these sub stay in the same module because they share a variable. hope this helps with your project

VBA Code: 
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
 
Upvote 0
diddi said:
hi. make sure all of these sub stay in the same module because they share a variable. hope this helps with your project

VBA Code: 
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
Click to expand...
Sir I added this to module
VBA Code: 
Public SelectedTextBox As String
and it is work! Thank you so much ??❤️
 
Upvote 0
woohoo! well done to you.
post back if you get stuck again :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Show the largest date and the smallest date on the listbox Userform
Replies
5
Views
133
Domenic
D
I
  • Question
Gather data for Listbox only after a certain date
Replies
2
Views
285
ipbr21054
I
P
  • Question
Data not copying fully into ListBox2
Replies
0
Views
107
Patriot2879
P
T
  • Question
print out all of PDF files based on showing in listbox on userform
Replies
0
Views
555
tubrak
T
M
  • Question
drag and drop listbox that updates at worksheet
Replies
2
Views
602
monvarona
M

Forum statistics

Threads
1,214,829
Messages
6,121,826
Members
449,051
Latest member
excelquestion515

Share this page

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