Printing sheets selected from listbox

  • Thread starter Thread starter Medemper
  • Start date Start date
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?
 
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?
 
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?
 
Back
Top