TodoExceltips
14_04_07
Setting Print Ranges for Multiple Worksheets
Martin asked if there is a way to set print ranges for multiple worksheets at the same time. He has a workbook containing a number of worksheets structured exactly the same, and he wants their respective print ranges to be exactly the same.
As Martin has discovered, there is no way to do this directly in Excel. When you select multiple worksheets, select the area you want set as the print area, and then try to set the print area, you quickly discover that the option to do the setting is grayed out, so you cannot select that option.
There are several things you can try, however. One is to start with a new workbook and develop a single worksheet that contains the print area as you would want it on all worksheets. Then, copy the worksheet however many times desired in the workbook. The copied worksheets will have the print area set as it was in the first worksheet.
The other option is to create a macro that will do the print-area setting for you. Consider the following macro, which will set the print area for all the selected worksheets to whatever the print area is on the active worksheet. (When more than one worksheet is selected, the active worksheet is the one that is visible when you run the macro.)
Sub SetPrintAreas1() Dim sPrintArea As String Dim wks As Worksheet sPrintArea = ActiveSheet.PageSetup.PrintArea For Each wks In ActiveWindow.SelectedSheets wks.PageSetup.PrintArea = sPrintArea Next Set wks = Nothing End Sub
If you prefer to have the print area set to some range that you specify, rather than needing to set the print area on the active worksheet first, then you can make one small change to the macro so that it uses a range for the print area:
Sub SetPrintAreas2() Dim sPrintArea As String Dim wks As Worksheet sPrintArea = "A7:E22" For Each wks In ActiveWindow.SelectedSheets wks.PageSetup.PrintArea = sPrintArea Next Set wks = Nothing End Sub
To choose a different print area for your needs, replace the range that is assigned to the sPrintArea variable. If you figure that you may use the macro quite a bit, in a number of different workbooks, or if you figure that you may need to change the print area regularly, you could change the macro so that it prompts the user for a range to use:
Sub SetPrintAreas3() Dim sPrintArea As String Dim wks As Worksheet sPrintArea = InputBox("Enter print area range") For Each wks In ActiveWindow.SelectedSheets wks.PageSetup.PrintArea = sPrintArea Next Set wks = Nothing End Sub
+++++++++++++++++++++++++++++++++++++++++++++++
Opening a Workbook but Disabling Macros
Bob is processing information in a workbook by using a macro. He would like for the macro to open a second workbook that has an AutoClose macro in it, but he doesn't want it to run when the second workbook is closed. He is looking for a way to open the second workbook, under the control of the macro in the first workbook, without enabling the macros in the second workbook.
There is no way to disable the macros in the second workbook when opening it under macro control. (If you are opening it manually, you can obviously hold down the Shift key as the workbook opens, but that doesn't help your macro--it has no fingers to hold sown that key!)
There are a couple of workarounds, however. The first involves modifying your code that closes the second workbook, in this manner:
Application.EnableEvents = False Workbooks("SecondBook.xls").Close Application.EnableEvents = True
By setting the EnableEvents property to False, the event that is going to happen (closing the workbook) will not trigger the AutoClose macro. You can (and should) then set the EnableEvents property to True so that events can later continue.
Another workaround is to set some sort of "flag" in the AutoClose macro of the second workbook. This flag could test to see if the first workbook is open, and if it is, not run the main code in the AutoClose macro.
To do this, in the second workbook at the top of the module pages add the following code:
Dim AutoCloseDisabled as Boolean Sub DisableAutoClose() AutoCloseDisabled=True End Sub
Note that the declaration statement for the AutoCloseDisabled variable is outside of any procedure, which means that it will be global in scope and accessible within all the procedures.
Next, modify the AutoClose macro so that its body is enclosed within an If statement, as shown here:
Sub AutoClose() 'variable declarations here If Not AutoCloseDisabled then 'body of AutoClose here End if End Sub
The idea is that when the second workbook is opened normally, the AutoCloseDisabled variable will be automatically set to False. (Boolean variables default to False when they are declared.) Since the DisableAutoClose procedure is never run in the workbook, the If statement in the AutoClose macro allows the actual body of the macro to be executed.
If you open the second workbook from your first workbook, then the code in your first workbook can call the DisableAutoClose macro in the second workbook, thereby setting the AutoCloseDisabled flag to True. This means that when the second workbook is closed, the If statement will skip over the body of the AutoClose macro.
++++++++++++++++++++++++++++++++++++++++