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


ADVERTISEMENT

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 View Related

Set Window Size With VBA.

Feb 1, 2010

I'm looking for a way to use VBA to set the window size upon opening the workbook. When the document opens, there should be two windows, each taking up half the screen and each on a particular tab.

Currently, if I save the workbook with the window settings I want, when I open it, both windows are maximized. When I hit the restore button, they go back the settings with which they were saved, which is what I want it to look like as soon as it's opened. Also, a bonus would be for the code to determine the screen size, so it can fill the screen no matter what monitor/computer it's opened on.

View 4 Replies View Related

Window Size And Position

Mar 2, 2007

I am having difficulty locating / composing a script that will set the Main Excel window size? Even better if I could find a way to set the size and position on screen..

View 2 Replies View Related

Set Window Size Based On Screen Resolution

Mar 5, 2008

I have an Excel based application that needs to run with a screen resolution of 1024*768 in order to display correctly. Is it possible to use VBA to detect the users screen resolution. Then: If the screen resolution is not set to 1024*768 then resize Excel's main window to run at that width and height?

View 2 Replies View Related

Excel 2007 :: Control Positioning And Size Of Window?

Mar 13, 2012

My set up office 2007 win xp pro ie 8 I am using the code below to retrieve web pages etc it works fine, however it display in differ areas of the screen, so I was wondering if there is a way to control the positioning and the size of the window?

Code:
Set EXP = CreateObject("InternetExplorer.application")
EXP.Visible = True
EXP.Navigate (aurl)

[Code]....

View 2 Replies View Related

Set Initial Userform Font Size Through Properties Window Instead Of Code

Feb 8, 2014

Instead of coding it (like below), can you set it in the properties window for labels, buttons.. etc.

[Code] ....

View 2 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

Cells Becoming Unselected When Window Focus Shifts

Aug 4, 2007

I would like to run some code against the currently selected range of cells in the worksheet, but when I click on the VB button that runs the code, the focus shifts from the worksheet to the VB app, and the cells in the worksheet are no longer selected.

I know this is probably not a question that's come up before, but does anyone have any idea how to retain the 'selected' status of the cells in the worksheet ?

View 9 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

VBA To Split Size Of Cells

Jun 15, 2006

I have created a VBA which organizes data from duplicate accounts into a single row so that it is properly formatted for our email system. The one issue I am having trouble handling is cell overflow. Column B cannot end up with more than 5 pieces of information. Each piece of info is seperated by a comma. If there is overflow, the extra data needs to be moved to the following column.

With the example I have provided, you will see that after running the macro, cell B2 has 18 pieces of information. I need to expand the macro so B2 contains 5 pieces, C2 contains 5 pieces, D2 contains 5 pieces, etc.

View 6 Replies View Related

Different Size Cells In Same Column / Row

Feb 25, 2008

Is it possible to have different size cells in a single column. For column A I would like to have a width of 30 up to row 20 and then after row 20 I would like a width of 15.

View 3 Replies View Related

Setting Size Of Array Using Cells (VBA)

Dec 12, 2013

I'm trying to set a range variable ('IngFore') as equal to a number of rows long and a number of columns wide via 'Cells'. The range needs to be dynamic in that the total number of rows and columns will vary over time. Hence, I've defined 'lastdate' to determine the last row in the array, and 'lastcol' to determine the last column in the array. The starting point for the array is I12.

It would appear that I can't quite get the syntax right.

Code:
Set IngFore = (Cells(12,9 to lastdate,9)),(Cells(12, 9 to 12, lastcol))

I've tinkered with it here and there, removing and adding brackets but I can't get it to work.

View 3 Replies View Related

Sorting Errors - Cells Size

Sep 12, 2008

I have a worksheet with a data base type list (16 cols, 10000 rows deep) which contains only 30 records, for testing purposes. Each cell is bordered in all 4 sides.

There will be a bunch or reports associated with this, requiring filtering and/or sorting. The macro instructions I applied so far are:

1) Locate last row.
2) Select the range from first to last row and apply a common row height (say, 24).
3) Sort as required
4) Filter as required.
5) Print

The sorting step has invariably failed.
The funny thing is that the code I used is the one produced by recording the steps. I am able to sort that segment manually, but the macro instructions fail.

here is the code...

Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Key2:=Range("H5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal

View 9 Replies View Related

Auto Size Cells Depending On Data In Them?

May 30, 2014

I am trying to auto size cells depending on the lenght of the data in them. I have added some code in the work book on the attached file, this works on cell that i maunally add data to any where on the active page (template) but the cells that are auto updated with currencey data dont expand?

If you double click column H a list appears select a test and this auto populates the charges accociated with it

Is there a way to do this as it just fills with #### ???

The code in this workbook is below

[code]
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim actCol, curCol

[Code]....

View 2 Replies View Related

Cells Will Not Always Increase In Size With Text Wrapping

Sep 25, 2013

My cells will not always increase in size with text wrapping.

Especially when I copy and paste from one sheet to another. I have formatted and re-formatted and re-formatted.

View 2 Replies View Related

Draw An Arrow Equal The Size Of The Selected Cells

Sep 16, 2008

I want to select some cells (like A2-A20) and have it draw an arrow down the selected cells. Tried a few different things, none of which worked...

View 14 Replies View Related

VB To Print Ranged Cells And Format To Readable Size?

Aug 15, 2013

I got a spreadsheet that has a range of used cells from C1:Q1000 but so far only C1:Q100 has been added data so far.

I am trying to get a VB to print range of cells but to fit in 1 page wide and still be readable.

Code:
Sub Print_Area()
Dim My_Range As String

On Error Resume Next 'enables error handling
My_Range = InputBox("Enter the name of the area to print:")

[Code] .......

I have found this piece of code above and works perfectly only thing missing is page formating

Code:
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = to be dynamic and print if data in cells in as many pages it needs
.Zoom = False
End With

View 3 Replies View Related

VBA To SHADE Cells Based On: Step-size & Starting Cell

Mar 4, 2009

I have a spreadsheet that will monitor payment schedules, in which both payment frequency and the payment start date are inputted by the user.

As such, to make filling out the column(s) fool-proof, I want to grey out cells in which data should not be entered.

For example, if the payment frequency is every 6th day, and the payments are to begin on day 0, then days 0, 6, 12 (etc) should be left white, whereas the remainder of the cells should be shaded.

I can achieve this using multiple conditional formatting rules in excel2007 with iterations of formulae of the type:

View 14 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

Hide / Unhide Columns With Selected Cells Change Font Size

Apr 21, 2014

I have recorded macro.

What I am trying to achieve is ....

When I select cell A2 and press command button Then - Columns C, D, E are unhidden

-Relative cells in selected row ( in this case C2,D2,E2) change font to 12

- When command button is pressed then C,D,E are hidden and font size goes back to 1

similarly if i select A3 same should happen to C3,D3,E3

Currently all is OK but when I press command button it all happens with the entire column C,D,E

Sample book attached.

Code is as follows:

[code]....

View 6 Replies View Related

Excel 2010 :: Removing Text From Mixed Font Size Cells

Aug 4, 2012

I have text of size 14 and 18 mixed in cells in a column. Cells are font size 18 or mixed with both 14 and 18 size text. I need to sort out the text with one column of size 14 and another of 18 only. I am thinking of copying and pasting the column twice and run a macro in first column to remove the text of size 14, and another macro to delete text of size 18 in second column. I need the leftover text to be in same rows.

I tried everywhere and couldn't find a macro for mixed text cells. I am using Microsoft Excel 2010.

View 9 Replies View Related

VBA Change Font Size Of A Character Or Letter Within Any String In Selected Cells

May 15, 2009

Looking for VBA that can change the font size of "•" char(0149) within any string in selected cells.

Also looking to delete the last "•" char(0149) within any string of selected cells.

What would the syntax for these two functions be?

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







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