Force Maximized Window & Zoom On Used Range

Oct 10, 2006

I have the following code working great, however, it only applies to the worksheet that opens when the .xls file first opens. I need it to also apply to all of the sheets in the workbook, in particular sheets titled TNT & Variables

Option Explicit

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.EnableEvents = True
Application.WindowState = xlMaximized
ActiveSheet.UsedRange.Select
ActiveWindow.Zoom = True
Range("salesperson2").Select
End Sub

View 4 Replies


ADVERTISEMENT

Display FullScreen After Window Maximized?

May 24, 2012

I know how to use Application.DisplayFullScreen = True to enlarge the screen upon openning the excel file. But, when you minimize the screen and then maximize it again, the full screen goes back to normal. How do you force the screen to stay Full Screen?

View 5 Replies View Related

Excel Maximized - Window Containing Cells Doesn't Fill The Space

Mar 2, 2014

Alright, so I have Excel itself maximized but the window containing the cells doesn't fill the space that it should... if this makes sense, its like the cells have become a subwindow or something ...

View 1 Replies View Related

How To Size App. Window To Fit Cells? (not Zoom)

Oct 16, 2009

This "smells" like a simple question, but I've had no luck finding a way to [programmatically] adjust the App.Window to fit around some cells.

I've found "Application.Goto", also the Application.Width/Height setting. The App.Width setting is pixel based, so if there's an easy way to know a cell's pixel-location, that'll work too!

View 10 Replies View Related

Force UserForm To Always Be Top Window

Oct 24, 2007

Is it possible to create a "floating" UserForm with VBA? That is, a form that is always on top af all the other windows. If not I think I will have to create an application in VB that imports data from the active Excel Spreadsheet, wich I have been lead to understand is fully possible. I really need this for the application that I am currently developing and I from what I have heard it is possible in VB.

View 4 Replies View Related

Zoom In By Using The ActiveWindow.Zoom Control

Nov 28, 2008

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%.

View 3 Replies View Related

Zoom Worksheets To Fit Used Range

Aug 28, 2007

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

LCol = Cells. Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
Range(Cells(1, 1), Cells(1, LCol)).EntireColumn.Select

' Need code help here to apply to all worksheets
ActiveWindow.Zoom = True ' this works for the worksheet activation event only

Next ws

End Sub

View 5 Replies View Related

Subscript Out Of Range: Activating Window

Aug 28, 2007

I'm using the below code to create a new excel file and activate it. But I'm getting the error message "Run-time Error'9': Subscript out of range" at Windows().activate command.

tdt = Format( Date, "dd-mm-yyyy")
nameoffile = "Recon Status" + tdt

Workbooks.Add
ActiveWorkbook. SaveAs Filename:=nameoffile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows(nameoffile).Activate

View 3 Replies View Related

Force Focus To Range From Modeless UserForm

Nov 21, 2007

A userform to move the curser to a column then activate the Excel App and keep the userform visiable.

The problem is when excution the code step by step (F8) works fine, but when excution the code by F5 the cursor moves to the Range("A65536").End(xlUp) and doesnot then move to the required column.

also this line
AppActivate ("Microsoft excel")

does not work , it gives me Invalid proceduers or call argument.

Private Sub ComboBox1_Change()

Range("A65536").End(xlUp).Activate
If ComboBox1.ListIndex = 0 Then Cells(ActiveCell.Row, "I").Activate
If ComboBox1.ListIndex = 1 Then Cells(ActiveCell.Row, "J").Activate
If ComboBox1.ListIndex = 2 Then Cells(ActiveCell.Row, "K").Activate

AppActivate ("Microsoft excel")

End Sub

View 9 Replies View Related

Run VBA Macros In Specific Order, Turn Off Calculations & Force Calculation Of Range

Apr 22, 2009

I have a VBA method that goes thru and calculates a bunch of stuff and puts this calculated info into a static data structure. This method is called 'setProjectInfo()'. This method is reliant on values on my worksheets, So as the user makes changes to the worksheet, I want setProjectInfo() to get called so it refreshes the data structure with updated information. I then have a bunch of helper methods to access different parts of this data structure. These helper methods are used in the formulae of a bunch of cells on a worksheet (ie '=getPercentComplete(period)', etc, etc).

This is all working well except for one thing. Sometimes the helper methods get called BEFORE setProjectInfo() gets called.. So, the helper methods return stale information. I can only get around this by manually recalculating the spreadsheet again. setProjectInfo() does enough stuff and takes a long enough time that I don't want to have to call it at the top of each helper method.

I've put the call to setProjectInfo() in a cell formula at the top of the my spreadsheet in hopes that Excel would know enough to call it first.. But that does not seem to work. Net, how can I get VBA to always call setProjectInfo() BEFORE it calls any other VBA methods?

View 3 Replies View Related

Custom Zoom Set At 90

Dec 23, 2008

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??

View 2 Replies View Related

Defualt Zoom

Jul 31, 2008

Im using Excel 2007

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.

View 9 Replies View Related

Copy Zoom To New Sheet

Jan 22, 2009

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?

View 3 Replies View Related

Screen Zoom For A Workbook

Jan 24, 2013

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?

View 9 Replies View Related

How To Zoom Sheet Particular To 80% Using VBA On Closing

Mar 21, 2013

When user close the file, I want to make sure certain sheets can zoom to 80%. How can this be done using VBA?

View 1 Replies View Related

Zoom To 120 When Any Workbook Is Opened?

May 13, 2014

I am looking for a way to have Excel zoom to 120% whenever any workbook is opened. (My boss has poor eye sight...)

I set up a simple Macro

Code:
Sub Auto_Open()
ActiveWindow.Zoom = 120
End Sub

but I get an error that says "Run-time error '91': Object Variable or With block variable not set.

View 1 Replies View Related

Code To Open WS In 70% Zoom

Nov 16, 2006

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%.

View 9 Replies View Related

Center On Zoom In A Frame

Jul 15, 2006

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

View 2 Replies View Related

Adjust Zoom With Scroll Bar

Feb 21, 2008

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

View 2 Replies View Related

Excel Macros To Zoom In And Out Shapes

Aug 10, 2014

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."

View 5 Replies View Related

Form Picture To Zoom-in On Mouse Over

Sep 17, 2009

I'm not sure if i've dreamt this or read it somewhere and if i have read i can't remember for the life of me.

I have a picture on a form of mine and what i want is when the user hovers over the picture, the image zooms in by x percent.

View 2 Replies View Related

How To Write An API To Control The Zoom Of A Worksheet

Apr 19, 2007

I want to know how to make a spreadsheet automatically control the zoom of a worksheet depending on the size of the screen it is opened on. For example, if it is opened on a screen of a certain size then it will set the zoom to 80% so the worksheet will show only a certain section. I want my worksheets to look the same on even the larger screens and not show the unused rows and columns around the section I am wanting to highlight.

In a previous thread I was told I might need to write an API to help me with achieving this. First of all, what is an API? Secondly, how do I go about writing one?

View 9 Replies View Related

Graphically Adjusting Zoom Using A Scroll Bar

Dec 14, 2007

I am created a very simple account plan for colleagues in my team which is proving to be very poular with the exception of one thing.

I created the plan which is around 30 sheets on my laptops highest resolution setting. 1400 x 1050. Only one other colleague has this res available so of course when the rest of the team open it, it looks huge.

I need help with this as I'm still very new to excel, although learning very fast!

My preference for finding a fix for this would be to insert a scroll bar on each sheet that, when scrolled, changes the zoom of the page between a range of 50% and 100%. This may not be the best way of doing it but I'm trying to make the sheets as simple to use as possible as some of my colleagues are only just compuetr literate, let alone excel competent!

View 9 Replies View Related

Dropdown Text Very Small At 55% Zoom

Aug 20, 2008

I have a form that has been set up at 55% for best view. The only problem I have is that all the dropdown boxes (List) that i have the text is extremly small. Is there anyway of changing the size of this text? just the size of the text in the dropdown not in the cell after the selection is made.

View 9 Replies View Related

Page Setup And Zoom Property

Nov 19, 2003

I was curious if in VBA is there a way to switch in the page setup from Fit to X Page by X Page to the Scaling %.

I have set all my pages to fit 1 x 1 and would now like to know the scaling % (Zoom) of the sheets.

View 9 Replies View Related

Scroll & Zoom Sheets Simultaneously

Dec 8, 2006

I have a workbook of 6 Sheets, what i want is that if i scrolled or zoomed one of the sheets i want all other sheets to be scrolled and zoomed accordingly identically.

to explain more: say I'm on "sheet1" and I scrolled down so i'm seeing range(K225:X250), now if i switched to any of the other sheets i want them all scrolled to the same range. so whatever sheets i choose i want to see range(K225:X250) in the same viewing properties "zooming".

View 9 Replies View Related

Insert Image - Center & Zoom

Jan 25, 2007

I have a worksheet (Covers) that is going to be the front and back covers for a binder.

The title/text portion is automatically filled from cells elsewhere in the workbook.

I have a button to bring up a userform that allows one to select the images (3 .jpg images) you wish to use on the cover pages.

I have code that successfully brings up the userform, and allows image selection and preview within the userform.

Now I want to insert these selected images into the worksheet, centered on specified cells and sized (zoom) to a maximum width/height (whichever is reached first).

Can a blank Picture be inserted with its size specified/fixed, then use code (Image1 = LoadPicture?) to change what is displayed?

Or do I have to calculate the size, center it on the page, and insert the image each time?

The code (in part) for the UserForm looks like:

Private Sub SelFcvrImg_Click()

Dim FCpicName As Variant
ChDir ("S:DanBuilder Logos-Photos")
FCpicName = Application. GetOpenFilename(Title:="Select an Image!", _
fileFilter:="Pictures (*.bmp;*.gif;*.tif;*.jpg),*bmp;*gif;*.tif;*.jpg")
If FCpicName <> False Then InsertImgForm.FCoverImgPrvw.Picture = LoadPicture(FCpicName)

End Sub

The UserForm has an Image (preview) with PictureSizeMode set to zoom.

I'm really after the same thing embedded in the sheet...

View 9 Replies View Related

Capture Print Zoom Value Before Printing

Apr 19, 2008

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

End Sub

View 3 Replies View Related

Protected Zoom Level For All Sheets For All Users?

Dec 22, 2013

I've created a shared spreadsheet with multiple sheets. All the content was created to be visible without having to scroll at 87%. My question is can I protect or lock the zoom level so that when anyone else opens the spreadsheet, it will only be visible at 87%? I have my screen resolution at "Smallest" so not sure if that too will affect the viewing level.

View 7 Replies View Related

How To Select Cell A1 After Zoom / Center Macro

Mar 2, 2012

I'm having to run at low resolution on a sheet currently, so I'm using a zoom/center routine to make the dropdown lists visible for selections.

Issue I have is how do I select cell A1 after my error handling and exit routine properly. I couldn't get it to work without trying to select cell A1 before I would make a dropdown list selection. For now I am just running a separate macro to select a1 tied to a pushbutton as a temp fix. Code is attached.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lZoom As Long
Dim lZoomDV As Long
Dim lDVType As Long
lZoom = 57

[Code] .............

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved