Print macro

[Steph]

Hi everyone. Does anyone have a snazzy print macro that will allow the user
to select the sheets to print from a dropdown list? I'm not worried about
the ranges to print, just to allow the user to select 1 or many sheets (like
a multi-select) to print. Thanks!!

 

[kev_06]

I don't think there is any way to select mulitple items in a combobox,
but I do have code for printing out multiple items in a listbox. It
requires a command button and a listbox. The listbox is loaded with the
sheet names when sheet1 is activated. Here it is:

Private Sub CommandButton1_Click()
Dim myArr() As String
Dim wctr As Long
Dim Ndx As Long
Dim strname As String
Dim msg As String

With Me.ListBox1
wctr = 0
ReDim myArr(1 To .ListCount)
For Ndx = 0 To .ListCount - 1
If .Selected(Ndx) = True Then
wctr = wctr + 1
myArr(wctr) = .List(Ndx)
End If
Next Ndx
End With

If wctr = 0 Then
msg = MsgBox("Please select sheet(s) to print.",
vbExclamation)
Exit Sub
Else
ReDim Preserve myArr(1 To wctr)

Worksheets(myArr).PrintOut
End If

Sheet1.Select
End Sub

Private Sub Worksheet_Activate()
Dim intsheets As Integer

ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.Clear

intsheets = 1

Do While intsheets < (Sheets.Count + 1)
ListBox1.AddItem Worksheets(intsheets).Name

intsheets = intsheets + 1
Loop
End Sub

There's many ways to manipulate the code to load different values in
other than the sheet names (such as values from cells in the
worksheets). If you have any problems, let me know.

 

[Steph]

Sweet! Thanks Kev!!

 

[Steph]

Kev,

Can I ask one follow up question? The event macro that populated the list
box with the sheet names - is there a way to only have the listbox populated
with Visible sheets, ignoring the hidden sheets? Thanks!

 

[Dave Peterson]

This routine populates the listbox:

Private Sub Worksheet_Activate()
Dim intsheets As Integer

ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.Clear

intsheets = 1

Do While intsheets < (Sheets.Count + 1)
ListBox1.AddItem Worksheets(intsheets).Name
intsheets = intsheets + 1
Loop
End Sub

You could change it to:

Private Sub Worksheet_Activate()
Dim intsheets As Integer

ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.Clear

intsheets = 1

Do While intsheets < (Sheets.Count + 1)
if worksheets(intsheets).visible = xlsheetvisible then
ListBox1.AddItem Worksheets(intsheets).Name
end if
intsheets = intsheets + 1
Loop
End Sub

(Untested, so watch out for typos!)

 

[Steph]

Perfect. Thanks Dave!

This routine populates the listbox:

Private Sub Worksheet_Activate()
Dim intsheets As Integer

ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.Clear

intsheets = 1

Do While intsheets < (Sheets.Count + 1)
ListBox1.AddItem Worksheets(intsheets).Name
intsheets = intsheets + 1
Loop
End Sub

You could change it to:

Private Sub Worksheet_Activate()
Dim intsheets As Integer

ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.Clear

intsheets = 1

Do While intsheets < (Sheets.Count + 1)
if worksheets(intsheets).visible = xlsheetvisible then
ListBox1.AddItem Worksheets(intsheets).Name
end if
intsheets = intsheets + 1
Loop
End Sub

(Untested, so watch out for typos!)