Printing sheets selected from listbox

M

Medemper

This is probably something someone has done a long time ago, but can't seem to figure it out.

I have populated a listbox with the names of all the sheets in a workbook, and want the user to select sheets they want to print.

They would like to print them as an array for page numbering and previewing purposes.

I populate the listbox with:

Sub PrintPickList()
Dim sh As Worksheet
For Each sh In Worksheets
Printlist.ListBox1.AddItem sh.Name
Next sh
Printlist.Show
End Sub

I then take the selections and want to print them, but following gets an subscript error message:

Private Sub CommandButton1_Click()

Dim PrintString, strComma, strQuote As String
Dim intCounter
intCounter = 0
strComma = ", "
strQuote = """"
PrintString = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
PrintString = PrintString & strComma & strQuote & ListBox1.List(i) & strQuote
intCounter = intCounter + 1
End If
Next i
PrintString = Right(PrintString, Len(PrintString) - 2)
Unload Me
Sheets(Array(PrintString)).Select 'error here
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Also tried:

Private Sub CommandButton1_Click()
Dim intCounter
Dim PrintString() As Worksheet
intCounter = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
intCounter = intCounter + 1
ReDim Preserve PrintString(intCounter)
PrintString(intCounter - 1) = ListBox1.List(i) 'error here
End If
Next i
Unload Me
Sheets(PrintString).Select
ActiveWindow.SelectedSheets.PrintPreview

End Sub

but get object variable or with block variable not set error.

Any suggestions?
 
R

Ron de Bruin

Hi Medemper

Try to use this example

You can use a userform with a listbox and a button on it
Add this code in the Userform module
In the properties of the listbox set Multiselect to 1

Private Sub CommandButton1_Click()
Dim arr() As String
Dim N As Integer
N = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = ListBox1.List(i)
End If
Next i
If N = 0 Then
MsgBox "You must select at least one Sheet"
Exit Sub
End If
ThisWorkbook.Worksheets(arr).PrintOut
End Sub

Private Sub UserForm_Initialize()
For Each ws In ActiveWorkbook.Sheets
If ws.Visible = True Then
Me.ListBox1.AddItem (ws.Name)
End If
Next
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




This is probably something someone has done a long time ago, but can't seem to figure it out.

I have populated a listbox with the names of all the sheets in a workbook, and want the user to select sheets they want to print.

They would like to print them as an array for page numbering and previewing purposes.

I populate the listbox with:

Sub PrintPickList()
Dim sh As Worksheet
For Each sh In Worksheets
Printlist.ListBox1.AddItem sh.Name
Next sh
Printlist.Show
End Sub

I then take the selections and want to print them, but following gets an subscript error message:

Private Sub CommandButton1_Click()

Dim PrintString, strComma, strQuote As String
Dim intCounter
intCounter = 0
strComma = ", "
strQuote = """"
PrintString = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
PrintString = PrintString & strComma & strQuote & ListBox1.List(i) & strQuote
intCounter = intCounter + 1
End If
Next i
PrintString = Right(PrintString, Len(PrintString) - 2)
Unload Me
Sheets(Array(PrintString)).Select 'error here
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Also tried:

Private Sub CommandButton1_Click()
Dim intCounter
Dim PrintString() As Worksheet
intCounter = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
intCounter = intCounter + 1
ReDim Preserve PrintString(intCounter)
PrintString(intCounter - 1) = ListBox1.List(i) 'error here
End If
Next i
Unload Me
Sheets(PrintString).Select
ActiveWindow.SelectedSheets.PrintPreview

End Sub

but get object variable or with block variable not set error.

Any suggestions?
 
M

Medemper

Thank you very much!
Hi Medemper

Try to use this example

You can use a userform with a listbox and a button on it
Add this code in the Userform module
In the properties of the listbox set Multiselect to 1

Private Sub CommandButton1_Click()
Dim arr() As String
Dim N As Integer
N = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = ListBox1.List(i)
End If
Next i
If N = 0 Then
MsgBox "You must select at least one Sheet"
Exit Sub
End If
ThisWorkbook.Worksheets(arr).PrintOut
End Sub

Private Sub UserForm_Initialize()
For Each ws In ActiveWorkbook.Sheets
If ws.Visible = True Then
Me.ListBox1.AddItem (ws.Name)
End If
Next
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




This is probably something someone has done a long time ago, but can't seem to figure it out.

I have populated a listbox with the names of all the sheets in a workbook, and want the user to select sheets they want to print.

They would like to print them as an array for page numbering and previewing purposes.

I populate the listbox with:

Sub PrintPickList()
Dim sh As Worksheet
For Each sh In Worksheets
Printlist.ListBox1.AddItem sh.Name
Next sh
Printlist.Show
End Sub

I then take the selections and want to print them, but following gets an subscript error message:

Private Sub CommandButton1_Click()

Dim PrintString, strComma, strQuote As String
Dim intCounter
intCounter = 0
strComma = ", "
strQuote = """"
PrintString = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
PrintString = PrintString & strComma & strQuote & ListBox1.List(i) & strQuote
intCounter = intCounter + 1
End If
Next i
PrintString = Right(PrintString, Len(PrintString) - 2)
Unload Me
Sheets(Array(PrintString)).Select 'error here
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Also tried:

Private Sub CommandButton1_Click()
Dim intCounter
Dim PrintString() As Worksheet
intCounter = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
intCounter = intCounter + 1
ReDim Preserve PrintString(intCounter)
PrintString(intCounter - 1) = ListBox1.List(i) 'error here
End If
Next i
Unload Me
Sheets(PrintString).Select
ActiveWindow.SelectedSheets.PrintPreview

End Sub

but get object variable or with block variable not set error.

Any suggestions?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem converting from VB6 to VB.net 2
Listing xls files and sheets (excluding Hidden tabs) help 2
printer exception, error handler, need to loop through network num 4
Listing properties 2
Context menu for chart is missing 1
list box remove item by combobox 4
LINEST - "Expression too complex" 7
How to pass file references to a sub in VBA 1

Top