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 ?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

diddi

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

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
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
Sir I added this to module
VBA Code:
Public SelectedTextBox As String
and it is work! Thank you so much 🙏🏻❤️
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,327
Office Version
  1. 2010
Platform
  1. Windows
woohoo! well done to you.
post back if you get stuck again :)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,910
Messages
5,655,902
Members
418,250
Latest member
Jebacmakro

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