Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print macro
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print macro
Sweet! Thanks Kev!!
"kev_06" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print macro
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print macro
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print macro
Perfect. Thanks Dave!
"Dave Peterson" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
Macro to open print window and set to print entire workbook | Excel Discussion (Misc queries) | |||
Create a print macro that would automatically select print area? | Excel Worksheet Functions | |||
Why does macro speed slow after Excel Print or Print Preview? | Excel Programming | |||
Need Help w/ Print Macro to Print All Visible Sheets (including Charts) in a Workbook | Excel Programming |