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!! |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 05:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com