Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Macro
I want to print an array of sheets that are selected by check boxes so the pages print in order. Here is my code, but I get an error in the array, can someone help with this? Thanks!! Private Sub Cmd_Print_Click() Dim A As String Dim B As String Dim C As String If CheckBox1.Value = True Then A = "Sheet1" End If If CheckBox2.Value = True Then B = "Sheet2" End If If CheckBox3.Value = True Then C = "Sheet3" End If F = A & B & C Sheets(Array(F)).Select ActiveWindow.SelectedSheets.PrintOut End Sub -- LaraBee ------------------------------------------------------------------------ LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275 View this thread: http://www.excelforum.com/showthread...hreadid=499436 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Macro
Are these checkboxes on a worksheet (from the Control Toolbox toolbar) or are
they on a UserForm (designed in the VBE)? If they're on a worksheet: Option Explicit Private Sub Cmd_Print_Click() Dim shtCtr As Long Dim myArr() As String Dim selCtr As Long selCtr = -1 For shtCtr = 1 To 3 If Me.OLEObjects("checkbox" & shtCtr).Object.Value = True Then selCtr = selCtr + 1 ReDim Preserve myArr(0 To selCtr) myArr(selCtr) = "Sheet" & shtCtr End If Next shtCtr If selCtr = -1 Then MsgBox "None selected" Else Worksheets(myArr).PrintOut preview:=True End If End Sub If they're on a UserForm, change this line: If Me.OLEObjects("checkbox" & shtCtr).Object.Value = True Then to If Me.Controls("checkbox" & shtCtr).Value = True Then LaraBee wrote: I want to print an array of sheets that are selected by check boxes so the pages print in order. Here is my code, but I get an error in the array, can someone help with this? Thanks!! Private Sub Cmd_Print_Click() Dim A As String Dim B As String Dim C As String If CheckBox1.Value = True Then A = "Sheet1" End If If CheckBox2.Value = True Then B = "Sheet2" End If If CheckBox3.Value = True Then C = "Sheet3" End If F = A & B & C Sheets(Array(F)).Select ActiveWindow.SelectedSheets.PrintOut End Sub -- LaraBee ------------------------------------------------------------------------ LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275 View this thread: http://www.excelforum.com/showthread...hreadid=499436 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Macro
Thanks for your response. It is on a worksheet, not a form. The only problem is that the real sheets and checkboxes are not named 1,2,3,etc. I just posted that way for simplicity. How would I set this up if the sheets and checkboxes just have random text names? -- LaraBee ------------------------------------------------------------------------ LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275 View this thread: http://www.excelforum.com/showthread...hreadid=499436 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Macro
Don't use random names for the checkboxes. Use checkbox1, checkbox2, ...
And put the name of the worksheet in the caption of each checkbox. Then the macro can pick up the name from that caption. Option Explicit Private Sub Cmd_Print_Click() Dim shtCtr As Long Dim myArr() As String Dim selCtr As Long Dim OLEObj As OLEObject selCtr = -1 For shtCtr = 1 To 3 Set OLEObj = Me.OLEObjects("checkbox" & shtCtr) If OLEObj.Object.Value = True Then selCtr = selCtr + 1 ReDim Preserve myArr(0 To selCtr) myArr(selCtr) = OLEObj.Object.Caption End If Next shtCtr If selCtr = -1 Then MsgBox "None selected" Else Worksheets(myArr).PrintOut preview:=True End If End Sub LaraBee wrote: Thanks for your response. It is on a worksheet, not a form. The only problem is that the real sheets and checkboxes are not named 1,2,3,etc. I just posted that way for simplicity. How would I set this up if the sheets and checkboxes just have random text names? -- LaraBee ------------------------------------------------------------------------ LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275 View this thread: http://www.excelforum.com/showthread...hreadid=499436 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Macro
PERFECT!!!! Thank you so much! -- LaraBee ------------------------------------------------------------------------ LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275 View this thread: http://www.excelforum.com/showthread...hreadid=499436 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Macro
Woohoo!!!
Glad it worked for you. LaraBee wrote: PERFECT!!!! Thank you so much! -- LaraBee ------------------------------------------------------------------------ LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275 View this thread: http://www.excelforum.com/showthread...hreadid=499436 -- 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 |