Print macro

Steph · 06-29-2006, 11:50 AM

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** · 06-29-2006, 12:55 PM

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 · 06-29-2006, 03:45 PM

Sweet! Thanks Kev!!

"kev_06" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile:
> http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=556973
>

Steph · 07-06-2006, 01:25 PM

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!

"kev_06" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile:
> http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=556973
>

Dave Peterson · 07-06-2006, 02:55 PM

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 wrote:
>
> 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!
>
> "kev_06" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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.
> >
> >
> > --
> > kev_06
> > ------------------------------------------------------------------------
> > kev_06's Profile:
> > http://www.excelforum.com/member.php...o&userid=35046
> > View this thread: http://www.excelforum.com/showthread...hreadid=556973
> >

--

Dave Peterson

Steph · 07-06-2006, 03:00 PM

Perfect. Thanks Dave!

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> 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!
>>
>> "kev_06" <[email protected]> wrote in
>> message news:[email protected]...
>> >
>> > 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.
>> >
>> >
>> > --
>> > kev_06
>> > ------------------------------------------------------------------------
>> > kev_06's Profile:
>> > http://www.excelforum.com/member.php...o&userid=35046
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=556973
>> >
>
> --
>
> Dave Peterson