I have a number defined print ranges. I wish to create a list of all the named print ranges and then select certain ranges to print in a single document. I have been told can use something called a "userform".
View 4 Replies
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
I've found some code which works to print certain pages with value in cell A1 but I need to print dynamic ranges on some of the sheets as they will have filters on so the rows ranges will be different each time.
So far this is what I have but the dynamic range part is not working:
VB:
Sub Print_All_Worksheets_With_Value_In_A1()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
[Code] ....
I Have a Userform which Have My Data i Print User Form Using Print Command Button And My Code Is
[Code] ......
But Its Printout Whole Form I only Wants To Print contents of Text Box's Or only Text From Userform TextBox. How To Print out Only Content of User Form Not The Whole Form ...
I am trying to create a simple code that will allow me to print a range that will change according to the initial inputs. The columns will never change, however the rows will.
So far I have:
Sub PrintLog()
Dim Printer As Range
Set Printer = ActiveSheet.Range("a9", Range("e65536").End(xlUp))
Printer
End Sub
I want to create a very simple macro to print some ranges that I have defined in my workbook. The ranges are as follows:
Note: Each of these ranges is in a different worksheet named Cons_Summ, Esky_Summ, Indy_Summ, and Gfld_Summ respectively.
To be printed in portrait view:
- Cons_Summ_IS
- Esky_Summ_IS
- Indy_Summ_IS
- Gfld_Summ_IS
To be printed in landscape view:
- Cons_Summ_OH
- Esky_Summ_OH
- Indy_Summ_OH
- Gfld_Summ_OH
I just want to create a macro so I can run it and it will print all of the ranges listed above with the proper orientation. Nothing special, each range must fit on 1 page x 1 page.
What is the best way to declare non contiguos print ranges?
For instance I have the following
Sheets("Report").Range("C17:C33").Copy
But I want to add other print ranges like J5:K5
So I've tried this a few different ways and every time I get the 1004 error: "Unable to set PrintArea property of PageSetup class"
Basically I have a macro that goes through a workbook to hide certain rows and columns based on some user inputs. After doing this I would like to set up appropriate print areas on certain sheets so that when you go to print anything it comes out clean. I thought this would be easy but I'm stuck.
The Plans variable in the code below is an integer from the user input. Think of this code as grabbing two separate boxes and setting them as print areas, which I've know I can do manually because I've tried it.
What's wrong with the following code?? (I've also tried using the union function here, to no avail)
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2". If I replace WhatToPrint with "Report1,Report2" the print macro works.
Sub Macro2()
Dim WhatToPrint As String
WhatToPrint = Sheets("Reports").Cells(3, 6).Value
'sets the variable to equal the contents of cell D3 which contains the formula
'summarising the print ranges I want to print
Sheets("Reports").Cells(3, 6).Select
ActiveCell.FormulaR1C1 = WhatToPrint
' pastes the variable in cell F3 - just to check that it looks like I want it to
Sheets("Reports").PageSetup.PrintArea = WhatToPrint
'uses the variable to set print area - this is where it fails!
'if you replace the variable with the contents of cell F3 the macro will work
ActiveWindow.SelectedSheets.PrintPreview
End Sub
I am trying to write what I thought would be a simple macro to print out specific areas of my worksheets. I have shown the code below; the line causing the problem I have highlighted in RED. I am getting the following error message: "Select method of range class failed".
Reading other posts here. I think this may have something to do with the macro being assigned to a command button in one worksheet (AY114) and I am trying to get the macro to run on both the worksheet that the command button is in (AY114) as well as another worksheet (AY062).
Sub CommandButton1_Click()
Range("J2").Select
Sheets("AY114").Select
Range("A4:J53").Select
ActiveSheet.PageSetup.PrintArea = "$A$4:$J$53"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=45
Range("A56:M151").Select
ActiveSheet.PageSetup.PrintArea = "$A$56:$M$151"......................
I am working on building a better report manager using named ranges
1.) Loop through the workbook and find all named ranges
Output the named ranges to a worksheet
2.) Read the values from the worksheet and print each one
Seem simple enough and is really just first step
Would also like to store all print settings next to each named range to be modified by user as needed and used to format report before printing each range as needed
But I'm getting ahead of myself.
First is to print a named range
My code is below
Error msg:
Run Time Error 91
Object variable or With Block Variable Not Set
Debug points here:
Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value
1. Loop through all named ranges and output to worksheet. This part works fine:
Option Explicit
Sub GetNamedRange()
Dim nName As Name
Dim wb As Workbook
Dim wsPrnt As Worksheet
Dim Row As Long
With Application
. ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
On the attached sheet I would like to set the print range and then print based on 1 cell's valve. F9 is the value I would like to use:
If F9=1 I need it to print A1:M43
If F9=2 I need it to print A1:M87
If F9=3 I need it to print A1:M131 and so on.
I have a worksheet that has a few ranges and I need a printarea statement that looks like this:
Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$26,$G$1:$L$9,$M$1:$P$16,$Q$1:$S$7"
The above works, but each time I generate this worksheet, the ranges for the last row of each area can be dynamic.
So, I tried something like this:
Code:
Sub setPrtArea()
'set the print area
lr1 = Range("F65536").End(xlUp).Row
rngA = Range("$A$1:$F$" & lr1)
lr2 = Range("L65536").End(xlUp).Row
rngB = Range("$G$1:$L$" & lr2)
lr3 = Range("P65536").End(xlUp).Row
rngC = Range("$M$1:$P$" & lr3)
lr4 = Range("S65536").End(xlUp).Row
rngD = Range("$Q$1:$S$" & lr4)
ActiveSheet.PageSetup.PrintArea = rngA & "," & rngB & "," & rngC & "," & rngD
But, it fails. I have looked through many topics on this subject, but nothing seems to fit my scenario. This will pretty much complete my current project if I get this figured out and can export these print areas to pdf without a bunch of blank pages as I get now with no print area set.
What I have is a large number of sheets in a workbook (26 to be exact). Each of these sheets has one specific named range. The file itself is quite large so I would like to print these ranges to a single PDF file. I did my best to search for this topic in the forums and while I did find some macros that were close, there was some sort of piece of code missing. Also, will how I list the named ranges determine the order of how they will show up in the PDF File?
View 3 Replies View RelatedI have a dynamic print range defined as follows:-
=OFFSET(Sheet1!$A$1,0,0,MATCH("PRINT_AREA_ROWS",Sheet1!$A:$A,0),MATCH("PRINT_AREA_COLUMNS",Sheet1!$1:$1,0)), lets say this equates to range("A1:D12")
I also have a workbook beforeprint event defined as follows:-
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "Pete"
End Sub
My problem is that after the Print Event has fired , the dynamic range has been converted into a static one i.e range("A1:D12"). I have tried this on all version of Excel from 97 to 2007 and it behaves the same so premably this must be by design rather than a bug?
Am I doing something wrong? What is the work around? I'd rather not have to create the dynamic range within the print event procedure unless I have to as my workbook has many, many tabs!
Once a user of my workbook has input various price levels on the Input Page, the data is sorted on the output page. As the data on the Output Page is quite vast and not all of it is relevant on a particular day, the user is interested in just printing a certain range of data that are in 5 columns. I am trying to figure out how to set up a macro that will allow the user to enter into an Input Box the range to print (ie. Cells C25:G60).
I found one example on this site listed below that allows one to do that but I would like to make it more user friendly. I do not want to make the user have to write out Excel cooridinates. Specifically by allowing the user to input in the Input Box a High price level in Column C and a Low Price level in Column C ; The macro will look in Column C for the highest instance of this High Price and begin the range down to the last instance of the Low Price (there can be several instances of the exact price level.) The macro would then create the print preview of the cells Cxx:Gyy.
Sub SelectPrintArea()
Dim PrintThis As Range
ActiveSheet.PageSetup.PrintArea = ""
Set PrintThis = Application.InputBox _
(Prompt:="Select the Print Range", Title:="Select", Type:=8)
PrintThis.Select
Selection.Name = "NewPrint"
ActiveSheet.PageSetup.PrintArea = "NewPrint"
ActiveSheet.PrintPreview
End Sub
There were 2 macros. One printed all of the defined ranges (40) on separate pages, and the other printed all the graphs ("charts" - 39) on separate pages. I tried to combine the VBA code to print each range and then the corresponding graph. Everything is still on separate pages, but it saved time because I didn't have to collate after printing. It seemed to work. Then I tried figure out if I could print them all to a specific tray of the printer as set in the workbook or as the printer default. Now both the combined macro and the original macro are giving errors.
Is the code I have correct to do what I am trying to do (print each range and then the graph all on separate pages)? Is there any way to put the output tray choice into the macro?
Sub Load_Data_Report()
'
' Load_Data_Report Macro (print all tables & graphs)
' Macro recorded 12/21/00 by xxx
'
' Keyboard Shortcut: Ctrl+j
'
[Code] ........
I have this code, which does what i want it to do but i want 5 userforms printed in a4 and 1 in a3, also i want to print them all in colour, as they contain graphs, and visually its poor when in black and white
I added in black and white = false, but it still prints black and white, i tried size = A3 and that wouldnt work either
[Code] .....
with printing a long userform? Unfortunately, the me.printform does not print out the whole form. It gets cut off because my form is too long and slightly too wide. I also tried to do a print screen, but unfortunately, because my form is too long, it will only do a snapshot of a portion of the form. In my case, on the bottom as that is where I have placed my command button.
Does anyone know how to bring the whole form out to excel or word? I don't mind doing that as long as I can print out the whole form?
On my excel 2007 UserForm I have a CheckBox with 18 possible or multiple selections of reports (word.docm)
what I would like to do is, when selected they would be sent to printer.
Here's what code I have so far, something is missing, I'm not sure
Private Sub CommandButton2_Click()
For x = 1 To 18
If Controls("checkbox" & x) Then
Select Case x
Case x = 1
worddoc = ActiveWorkbook.Path & "Inspection ReportsCover Page.docx"
Case x = 2
worddoc = ActiveWorkbook.Path & "Inspection ReportsClient Information.docx"
Case x = 3.....................
Is there a code to set the printing of a userform to landscape. The following code is good for a worksheet but doesn't work for a userform.
ActiveSheet.PageSetup.Orientation = xlLandscape
The following adaption of the above brings up an error.
Userform1.PageSetup.Orientation = xlLandscape
I have developed an extensive program running on excell but with the user only seeing userforms. I would now like to build in a "print screen" button, but have no idea how to do this.
What it needs to do is capture the userform and all its entries (as it is displayed on the screen) and send this to the printer to be printed as a picture.
I am assuming that there must be code available so that when ever the user clicks this button, the standard windows (or excell) print screen appears from which he will be able to choose the printer etc.
I have created a userform which user will fill in, but have come up against usual problem of not being able to print a copy. It has scroll bars so can't just use ctrl + alt + print screen method.
Thought i could get around problem, by recreating the form on worksheet by using activeX labels. I use a basic loop that goes through each of the required controls on the userform and exactly recreates it on the worksheet, using Active X Labels.
This all works fine, but when it gets to end of macro, the userforms just disappears and macro stops. No error message!! Also during the process of creating the axtive x labels i can't step through in VBA as it says its uable to break at that point!!
Private Sub NoPRINTBLANK_Click()
Dim percent As Integer
Dim Olob As OLEObject
Set ctrl = TICKSHEET.Controls
pgs = TICKSHEET.COMPS.Pages.Count
pgs = pgs - 1
For z = 0 To pgs
percent = (z + 1) / (pgs + 1) * 100 ' this is for a progress bar on sheet
progresser percent ' this runs small sub to alter progress bar
TICKSHEET.Repaint
Worksheets("SHT" & z + 1).Activate
ActiveSheet.OLEObjects.Delete...................
I know you don't usually print userforms & when you do I know the code, however, how can you print one page of a multipage userform when the page has a vertical scrollbar & you want to print the whole page, not just what is showing on screen?
View 2 Replies View Related1/ I enter manually a list of serial numbers in a column
2/ the latest cell gives the number Y (count) of data entered
3/ I need a macro to perform the following on a Form when I press a button:
a. the form is printed Y times and every times:
b. "Page x of Y" where x increments (Y is the value given by the item 2/)
c. the serial number cell shows values from the list one by one (item 1/)
So, that means I will have Y copies of the form where the page number starts at 1 and ends at Y, and a Serial number linked to the list (page 1 shows SN from line1, Page 2 SN from line 2, ... Page Y SN from line Y).
Is it possible to have a (print)preview shown in a textbox in a userform?
A layout has been made in a worksheet and content is added to some cells through Userforms (textboxes and comboxes).
1 Userform contains a big textbox and a "SEND"-button.
What I would like is to have a preview of the worksheet inside that textbox before they click send.
During this whole proces, the Excel -Worksheet is not visible (to prevent changes being made to the program)!
I created a macro that prints a label from a userform. The macro used to reside on an XP platform and performed without issues. IT decided they wanted to upgrade the machine to a Windows 7 platform. Now when the userform prints a small window pops up that looks like a print preview of the label, however it is transparent and has a window caption of 'Precut Label' as identified in the userform design. I am including a screenshot of the pop-up. The unusual thing about this is the window cannot be closed, moved or selected in any way shape or form. The only way to get it to close is to view the userform object in the vba editor. The line of code that triggers the print sequence as well as cause the pop-up is: frmLabel.PrintForm. This line of code is housed in the sheet1 object.
View 1 Replies View RelatedI have a form to allow users to select and print pages from a workbook.
Private Sub UserForm_Initialize()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
ListBox1.AddItem sht.Name
Next sht
Me.Height = 128
End Sub
Only problem is that this lists all worksheets in the form. Need to modifying it so that it does not list worksheets that are xlSheetVeryHidden.
I have a before print event that shows a userform - the user can then select which worksheets they want printed and select a command button. I then have a loop set up that loops through the selected workbooks and prints them. The problem is I am not sure how to tell VBA to print without it going back to the Before Print event userform.
View 9 Replies View RelatedI have a worksheet called "ComplaintData".
In this worksheet I have data in following range : "N1:V2".
N1 to V1 is headings and N2 to V2 is data transfered from listbox on userform.
I am trying to print this range via command button on my userform.
I am wondering anyone can help me with the code for selecting and printing this range when user clicks on the "Print" command button on my userform?
how I can format this print out to look different than the tabular format? I mean if possible I would like to print this in portrait, fit to one page and possibly not look like a table. (If it has to look like a table then I will have to go with landscape as the table wont fit portrait without getting too small and un-readable)
