In a previous, expired thread, the following code was suggested as a way to capture the page-setup zoom value after setting PagesTall and PagesWide. It works fine when single-stepping in the VB Editor and when run directly by the user, via a button click or Tools=>Macros...=>Run, but fails when run under a Worksheet_Activate() event call.
Does anyone understand why that is and/or have a fix or workaround?
Sub X()
'
Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{1,#N/A})"
Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})"
MsgBox "Zoom factor is " & ActiveSheet.PageSetup.Zoom
I know you can predefine the level of zoom you want by using the ActiveWindow.Zoom control. I'm trying to find some code to Zoom in by a particular percentage. For example +10% zoom, so if you are currently on 90%, excel zooms to 100%, click again and then get a zoom of 110%.
I am after a code that will sort out the below printing problem
I want excel to hide ALL OF column E from the printer, i want to see the information myself but when i press print, column E will not show on my printed page
i have found this Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Name = "Sheet1" Then Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False With ActiveSheet .Range("e1").EntireColumn.Hidden = True .PrintOut .Range("e1").EntireColumn.Hidden = False End With Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
but am not sure how to enter it, also if the code is correct. I have entered it using the alt+f11 then clicked on this sheet, and put it in there but it still dont work
I have to print this Excel in the following manner.
I have a table which is similar to one given below. All I need is to print these information in each sheet. For Example Coat sold by CA separately, Coat sold by FD separately, and so on (like the lines I have entered).
Is there any way I can create a Macro for this....
I'd like to set the print range based on the last row with text in specific columns. I found a couple of macros in this forum to adapt, but neither are working. Extra rows, which only contain conditional formatting, and other excluded rows and columns still print.
Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim MaxRow As Long, i As Integer MaxRow = 1 For i = 1 To 6 Cells(65536, i).End(xlUp).Select MaxRow = Application.WorksheetFunction.Max(MaxRow, ActiveCell.Row) Next i ActiveSheet.PageSetup.PrintArea = "$A$2:$F$" & MaxRow End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim LastRow As Long LastRow = ActiveSheet.Columns("A:F"). Find(What:="*" _ , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ActiveSheet.PageSetup.PrintArea = "$A$2:$F$" & LastRow End Sub
I have created a sheet to calculate a resturant bill that need to be printed for each customer. I am trying to creat a command that will print the sheet up to the last item in the bill "sheet2". So I need the printin area to change according to the number of itmes.
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
For I = 6 To 18 Sheets(I).Visible = xlSheetVisible PrintSubs Sheets(I) Sheets(I).Visible = xlSheetVeryHidden Next End Sub
When it starts printing it starts off with my "JOBCOM" sheet which is sheet20. why it would start printing with that one. That sheet shouldn't even print at all. It's only supposed to print sheets6 to sheet18.
We have a program that sorts Characters into separate racks. I need to print a label for each rack. I can set the print area to cover the whole rack area but what I do not want to do is print blank labels. As you can see I have blank labels at the end of the row, and occasionally there will be one in the middle of the row as well. I have attached a screen shot of the list that I need to print. Is there any way that I can set the printer to only print the label that does have information on it? or am i going to have to print the blank labels in the middle as well?
I've seen code to set the print area for a dynamic range. However, how can the print area be defined by these parameters:
A1 to one extra blank row below the last cell of data in column A (height), and last column with data in row 12 (width).
Here is what I'm starting with...
Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet. Names.Add Name:="Print_Area", _ RefersTo:=Range(Range("A1"), _ Range("A1:IV65536"). Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious, after:=Range("A1"))) End Sub
I have copied formulas in other columns down beyond what the last row of col. A will be. Also, this will need to be applied to about 7 sheets in one book.
I want to have code that will print everything down the sheet until it sees a blank in column A. I have included the code i am trying but it keeps giving me errors.
I have been trying to see if I can set up a single (A1) print button to create a selection box to select a name range and than print that range, but have been unable to figure out how to accomplish it. I have 26 name ranges called PP01 thru PP26 and I want to be able to select the range before printing.
She has a spreadsheet created in Excel 2003 (she is still using 2003) which contains an image with a textbox in the top left corner then all other cells contain data.
When user prints this spreadsheet all that prints is the image and the textbox.
I have cleared the print area reset the print area removed the picture (prints only the text box)
and none of these changes have changed what prints.
I have checked all printer setting and these appear normal.
previously user could print this spreadsheet fine and all other worksheets in the workbook print fine as well.
other users are still able to print to the same printer correctly from this spreadsheet.
I have over 800+ pages of chart that only takes up 6 columns and around like 9000+ rows.
I wanted to print this chart on paper and need hardcopies. However, the chart in its current setup prints only on the left half of the page leaving the right half empty.
How do i make use of the full space properly? Each chart has a "page number" on it so I want the chart to print continuously from one half of the page onto the next half and then the second page, third, etc.
Here is a visual demonstration of how things currently are and how i'd like to get them to be:
As you can see, This is the first of many charts and its numbered Page 9 and next one is page 10.
Pic1
How this looks when i try to print, it's only on the left side. right is all blank Pic2
How i want it to look like upon printing Pic3
As you can see in the last picture, once page 14 chart has no space it automatically continues chart on right side of page and then moves on to print rest.
I would like to know if there is a way to print several different workbooks at once but keeping my printing format which I would like to be Landscape and Fit to one page. Reason is simple as I work in a office where staff is handed in several jobs to do everyday. They finish the jobs and log all the info on the database. I log on to database and put all their daily diaries and because it is all over the place I have to go in each file and set printing preferences which takes an hour in the morning and hour in the evening. I could do with some sort of automation where all diaries are automatically printed in Landscape and Fit on one page.
I have a report that is generated from a manufacturing process that looks like the example below. the report is 40 pages long when all the data is printed. i am looking for a way to only print this range if a dimension is "out of tolerance". if the dimension is within tolerance, there is always the "garbage" text of plus and minus. if every row is "within tolerance" in the range the cells in the OutTol column would all contain the "garbage" text but it will not always be identical. so, in summary, actual OutTol values = print and all "garbage" = not printed.
If I send a workbook out with the zoom set at 90...will the zoom be at 90 when others open it.? Also, is there a worksheet event that I could insert to force a specific worksheet to always open with the zoom at 90??
How do i change the default zoom that excel views documents? Whatever view the last person saved the file as it opens in that zoom. I want it to open 75% zoom no matter what anyone saved the files as.
I have a sub that copies worksheet to a different blank sheet in a different work book. Is there a way for me to set the zoom value on the 2nd sheet (the one being copied to) the same as the 1st sheet?
I have just run into the situation where some users, magically and overnight , have widescreen monitors. This means the screen designs for 4:3 monitors show way too much on 16:9 monitors. I would like to put a control button on the opening page that gives them a choice of zoom, which usually seems to be 100% and 135%.
I'm thinking probably an IF sequence to parse their response and execute the appropriate zoom. But how can I make it effective for all the sheets in a workbook?
I could need a code (WS code I assume) that open the sheet in 70% zoom every time I open that sheet. For now I get in 75% every time I open it. I guess that WS code will bypass that default setting, or bug, or what ever resets it 75%.
I am developing a form for emergency dispatching. One of the features of this dispatch sheet is that there are several maps in it that open on a seperate form. The map form has 4 maps that are picture files in an image. these images are in a frame. The different images are selected using option buttons. Given the background above, my problem is this. I have code that zooms in 50% each time the Click event for the image is fired. wht i am trying to do is make the zoomed view center where i clicked. this is the code that i have so far but it doesn't work all that spectacular, the closer I zoom in the further out of center the place i clicked gets untill it is out of view.
Private Sub Layout_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) CurrentX = X CurrentY = Y End Sub
Private Sub PlantLayout_Click() If Frame1.Zoom < 400 Then Frame1.Zoom = Frame1.Zoom + 50 frmmap.Frame1.ScrollLeft = CurrentX - (Frame1.Width / 2) frmmap.Frame1.ScrollTop = CurrentY - (Frame1.Height / 2) frmmap.Repaint End Sub
to apply a zoom to Fit Selection on all worksheets upon file opening? I've read many posts on this and understand how to apply this via worksheet activation, but haven't discovered how to make this to happen to all worksheets when the file is opened.
Zooming the selection needs to happen in the workbook module due to additional sheets being added at random and because it will facilitate the flow of the meeting so that we don't have to wait for the user to select and zoom.
Private Sub Workbook_Open()
Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets
I've used various "zoom" searches and can't find what I need. My users have different amounts of toolbars so the amount of screen space changes. I need to zoom so 4 graphs show. Am looking to enable the user with a scroll bar where the user could click / slide the control to change the zoom size on the screen so they can see the 4 graphs at their preferred "zoom".
I would prefer a control on the sheet and link it to a macro (or line of code if that's the proper phrase) and let the user decide (some people will want bigger zoom due to eyesight etc).
Private Sub ScrollBarZoom_Change() ScrollBarZoom.Value = ActiveWindow.zoom.Value End Sub
But I get Runtime error '424': Object required. Auto Merged Post Until 24 Hrs Passes;Nevermind. I found it. Answer was:
Private Sub ScrollBarZoom_Change() ActiveWindow.zoom = ScrollBarZoom.Value End Sub
I am struggling to get a macro code for zooming in and out the shapes. I have a macro which fulfill half of my requirement. By assigning the below macro to the shape I am able to increase the current size to double by clicking on the shape however I need the same macro should retain the original size upon second click.
Here is my code to increase the size.
[Code] .....
"The requirement: Once after assigning the macro, on first click it should double the shape size and on second click it should retain the original size."
