printing list box in the user form

[baha17]

Hi Everyone,
Is there anyway only print listbox in the user form or save the list
box values as a text file? I have a listbox in a user form which
sometimes has listbox1.listcount is more than 40. When you see the
listbox its ok you can scroll down to see other values after
listcount
40 but cannot see all the values in the listbox when you printing.
Is there anyway to do that?
Your help is greatly appreciated

best regards,
Baha

 

[Dave Peterson]

This worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()

Dim TempWks As Worksheet
Set TempWks = Workbooks.Add(1).Worksheets(1)
With Me.ListBox1
TempWks.Range("a1").Resize(.ListCount - 1, .ColumnCount).Value _
= .List
End With

Me.Hide
With TempWks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
'close and don't save
.Parent.Close savechanges:=False
End With
Me.Show

End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.ColumnCount = 3
For iCtr = 1 To 5
.AddItem "A" & iCtr
.List(.ListCount - 1, 1) = "B" & iCtr
.List(.ListCount - 1, 2) = "C" & iCtr
Next iCtr
End With
End Sub

 

[baha17]

Hi Dave,
you are great thanks it all works fine for me.
Thanks again
regards,
baha

 

[baha17]

Hi Dave,
One more thing why on the printout I have #N/A on the right side of
the page?
it looks like displays error for every row in the list box
thanks again

 

[Dave Peterson]

I don't know.

Did you modify the code (change the columncount info)?

If you did modify the code, post your current version.

How do you populate the listbox and how many columns are there?

And one more--Any columns hidden in the listbox???