Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Set Window Size With VBA.

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Set Window Size Based On Screen Resolution
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 Replies!   View Related
Window Size And Position
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 Replies!   View Related
How To Size App. Window To Fit Cells? (not Zoom)
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 Replies!   View Related
Set Range Of Varying Size
I am try to Set a range to a variable. The problem is the range differs in size. The good thing is there is a blank cell before each group that I am attempting to set the range to. I know it should be possible to set the range to stop at the blank row. I am locating the correct row by the date and want to set the range to include the next three cells to the right and all the cells below them up to the blank row.

View Replies!   View Related
Set Font Size Depending On Length Of Text
When a number is entered in cell S3, it triggers formulas throughout the worksheet to populate the it with information from another sheet. If the length of the text in B6 is greater than 80, the font size for B6 only should be 8; if the length of the text in B6 is less than or equal to 80, the font size for B6 should be 10. Regardless of the length of the text in B6, the font size for the rest of the sheet should not be changed.

I tried the following
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$6" And Target.Count > 1 Then Exit Sub
Dim cnt As Long
cnt = Len(Cells(6, 2).Value)
If cnt > 80 Then Cells(6, 2).Font.Size = 8
ElseIf cnt < 80 Then
Cells(6, 2).Font.Size = 10
End If
End Sub

View Replies!   View Related
VBA Grab Active Window Index
Dim iStartBook As Long, iNewBook As Long
iStartBook = ActiveWindow.Index
iNewBook = ActiveWindow.Index
I'm trying to grab the "2" that .NewWindow generates. The above gives 1 both times, despite the obvious 2 in the caption after you run it. So what property works? What does .Index mean here?

View Replies!   View Related
Add A Textbox At The Current Position (selected Cell) With A Set Size, Fill Color, And Border Color
I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this:

View Replies!   View Related
Does A File With VBA Grow In Size
I created a new form for our field personnel to complete daily. They are telling me that over a few weeks the file grows in size. The file size is 335KB starting out end up in a few weeks being 3.5megs. Now they add on some other sheets and things which undoubatbly make the file size larger but I am wondering if there is anyway thet VBA could somehow make the file bigger as the open it each day over a few weeks? They are not adding in any VBA, they are only using the code which is provided, which is accounted for in the 335kb. It seems unlikely but i thought i would ask.

View Replies!   View Related
VBA To Split Size Of Cells
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 Replies!   View Related
Specify Size And Location On Screen Of Notepad Opened By VBA?
I'm using this code to open a text file. Is there a method to specify the size and location of the window that opens with the textfile?

I'm sure there may be a better method to open the textfile, so feel free to tell me how bad this code is.

View Replies!   View Related
Will VBA In Place Of A Function Reduce This Workbook Size
I know that VBA doesn't always slim down workbook size and calculations are calculations whether in VBA or Formula format; but in this instance, do you think VBA would be a better choice? This workbook is currently enormous in size and slow, and I think I can slim it down if I used the right VBA procedures. However, Im not certain what those procedures would be.

On two seperate worksheets in a workbook, I have a column that contains a formula that references a third sheet.

Sheet 1 = "Order"
Sheet 2 = "Turn-in"
Sheet 3 = "Bank"

Ok, here's the scenario: On the "Order" sheet, I have a list of orders from my company; on the "turn-In" sheet, a list of turn-ins. The "Bank" sheet lists ALL transactions (ORDER AND TURN-IN).

On both the turn-in and Order sheets, the formula is pretty much the same; it references a different column on the bank sheet, but does it the exact same way. Ok... The Order sheet lists all order from the company by order number (column A); the formula uses that order number and finds the transaction on the "Bank" sheet and returns the order cost as the result. however, the order numbers on the bank sheet have a 5 digit prefix on them (the prefix is the same on all orders, its used by the bank but only orders with my 5 digit prefix are on the list sent to me and pasted to this sheet. So all orders on bank sheet have the same prefix)

The below formula is in Cell C10 on "Order" Sheet; Column C lists the Cost reported by the bank. The formula below references the Bank sheet database, looks for an order number that matches the order number in A10 (Adding the prefix of course), and returns a cooresponding cost for that transaction.


I know this seems long for what it is, but it has to add the prefix, and then match the order number, and reference the cooresponding cost.

The kicker, this forumla is in more than 10,000 cells; 6,000 on the order sheet, and 5,000 on the turn-in sheet. Of course the references are a little different, but the principle is the same for all of them.

Is there a way to do this in VBA to save space and resources? This formula is really dragging.

View Replies!   View Related
VBA Change Font Size Of A Character Or Letter Within Any String In Selected Cells
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 Replies!   View Related
VBA To SHADE Cells Based On: Step-size & Starting Cell
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 Replies!   View Related
Display All VBA Code In Code Window (VBE)
When I started using VBA, all the code for each module was shown in the code window. This was a bit cumbersome when looking for one sub in particular. Now when I load VBA, all the Subs and Functions have their own page, and can be selected from the right hand drop down list. This is much easier for entering code, and finding subs. However, it is sometimes useful to see 'the big picture', so how do I swap between the two types of display?

View Replies!   View Related
Create From The Data Set In Column Set 1 The Data Set Shown In Colunm Set #2
My data is in colunm set #1. I want to create from the data set in column set 1 the data set shown in colunm set #2. I want to automate the process. The program needs to regognize if a name appears only once or more than once. If it appears more than once the data needs to be merged as I highlighted from yellow to green.

View Replies!   View Related
To Set Up A Condition Is Vba
Been teaching myself VBA and I've gotten stuck, and no matter how I search I can't seem to find an answer...

Trying to set up a condition is vba where if a cell value minus another cell value is greater than a static value (elsewhere in the sheet), then to turn the font green.

Code so far:

Dim Cval As Integer
Cval = Cells(3, 6).Value
FinalRow = Cells(65536, Cval).End(xlUp).Row
For i = 10 To FinalRow
If Cells(i, Cval) - Cells(i, Cval + 1) > R6C5 Then
With Cells
.Font.ColorIndex = 10
.Font.Bold = True
End With
End If
Next i
End Sub

I can't work out the right sintax to get a if condition to work correctly.

I have gotten this to work with conditional format, but if you want to copy/paste elsewhere, it doesn't paste the format result correctly...

View Replies!   View Related
Set Locations Using VBA
I have to manually cut/paste each excel doc which belongs to a specific Store folder, and copy then into their own shared directory, so for example:

Lets say my company has 10 Stores, and I know their online folder locations, the first step I guess would be for me to map the locations of each store, I am not sure what the best way to do this is, would I use the Case select ? to make the set = ?

So for example, Stores 1 - 10.

Set 1 = Dir("Z:Northwest1")
Set 2 = Dir("Z:Northwest2")
Set 3 = Dir("Z:Northwest3")
Set 4 = Dir("Z:Northwest4")
Set 5 = Dir("Z:Northwest5")
Set 6 = Dir("Z:Northwest6")
Set 7 = Dir("Z:Northwest7")
Set 8 = Dir("Z:Northwest8")
Set 9 = Dir("Z:Northwest9")
Set 10 = Dir("Z:Northwest10")

I am not an expert on Case Select, but was thinking:

Select Case myStores
Case Is = 1.......................

View Replies!   View Related
Set Protection Using VBA
I tried the following code and it doesn't work:

Sub Macro2()
Selection.Locked = True
Selection.FormulaHidden = False
Range(Range("A" & Rows.Count).End(xlUp).Row & "A300").Select
Selection.Locked = False
Selection.FormulaHidden = False
Selection.Locked = False
Selection.FormulaHidden = False
End Sub

I want to be able to unlock all cells after the last cell that has data in column A down to row 300. Also need to unlock cells AE11:AG300. What's wrong with my code?

View Replies!   View Related
Set A Date To The First With VBA
There's probably an easy way to do this, but I can't seem to get it to work. I'm trying to get vba code to look at a cell with a date in it, and if the cell is a date the day should be set to 1 (the first of the month).

View Replies!   View Related
VBA To Clean Data Set

A1Bad Data2Bad Data3Bad Data4Bad Data5Bad Data6Bad Data7Bad Data8Bad Data9Bad Data10Bad Data11Title12Good Data13Good Data14Good Data15Good Data16Good Data17Good Data18Good Data19Good Data20Good Data21Good Data22 23Bad Data24Bad Data25Bad Data26Bad Data27Bad Data28Bad Data29Bad Data30Bad Data31Bad Data32Bad Data33Bad Data34Bad Data35Bad Data

Excel tables to the web >>" target="_blank"> Excel Jeanie HTML 4

How can I ensure that all Rows before "Title" and all Rows after the last populated Row (denoted by the fact that there is a single blank Cell after it) are completely deleted?

View Replies!   View Related
Using CodeName In VBA To Set New Formula
I am writing a VBA macro to update a financial workbook. 10 of the sheets within the file are re-names depending on the start of production date. So, in one file they may be CY09, CY10, CY11...and then in another CY10, CY11, CY12. I need to update the Cell L14 on the first CY page to a value on the BOM page.

I want CYXX!F12 = BOM!L14
And CYXX's code name is Sheet3411

To do this, I want to use the code name - because it is the same in all the files I have (100+).

But I cannot find enough/right Codename reference information to make this work.

I tried:

Sheet3411.Range("F12").FormulaR1C1 = "=BOM!R[2]C[6]"

I would appreciate if someone could help with this particular issue. (A little explanation of when I can and cannot use the codename would be appreciated as well. As this is something I will be maintaining for years.)

View Replies!   View Related
Vba To Set Duplex Printing
I am trying to use code from a Microsoft site to set the printer to duplex, but it crashes right away. "Printer" is highlighted, and the error says "variable not defined." ...

View Replies!   View Related
Set VBA For Default Printer
I need an assistance in setting up a VBA for the default printer in my office. The issue is I want my spreadsheet to be set up to wherever I open the spreadsheet at any computers in the facility. I want the sheet to be printing out directly at the printer in my office only. Is there a way to do this?

View Replies!   View Related
Set Conditional Formatting Via VBA
I would like to add conditional formatting for the whole row based on whether the value of cells in column A is "CON".

For Each c In r
If c.Value = "CON" Then
c.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:=c.Address = "OK"
c.EntireRow.FormatConditions(1).Interior.ColorIndex = 35
end if
next c
the "CON" is a formula and can later take the value "OK".

The conditional formatting should highlight the whole row as green when that happens.

View Replies!   View Related
Check And Set References With VBA
for example, is it possible to check if a Reference to Set a reference to Microsoft Internet Controls (shdocvw.dll) has been enabled in user Reference settings. And if not set, then set it via VBA

View Replies!   View Related
Set Range For This Vba Code
Sub ReplaceUnderScores()
Selection.Replace What:="_", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
End Sub

I want it to do this for range AL2 through AL2000. Ive tried a few codes trying to get it to work, but cant figure it out.

View Replies!   View Related
Vba To Set Print Area
Looking for a macro that will set the print area on an accounts spreadsheet to the last non blank row, bearing in mind that all the rows contain formulas, have tried some macros but they assume the cells with formulas in them are non blank

View Replies!   View Related
Set Tools/Options From VBA
I use several different workbooks during the day, and sometimes I would prefer the " move selection after enter direction" option to = "Right" and sometimes "Down". It doesn't seem to be an option that is saved with the workbook.
what could be done to a workbooks code to change this option upon opening the book?

View Replies!   View Related
Set Font Color In VBA Script
I have a script that sets font color. I think it uses something called "myColor" to define the color, and then pulls the color from a palette of 57 colors.

But how can use the "color" property instead, so I can have a broader range of colors? I can't tell how it would fit into my code. "MyColor" doesn't even show up in Excel's help file.

Here is the code. How can I adjust it so that I can use, like, "RGB 0, 0, 255" instead of color 32?

Or how would you code it so you could set color that way?

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column < 8 Then Exit Sub
If Target.Column > 9 Then Exit Sub
Dim myColor As Variant
Select Case Target.Column...........

View Replies!   View Related
VBA Macro- Apply Set Of Commands
Sub elaseval()
For each cell in Range(B7:B50)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C & cell.Row : T & cell.Row").Select
Range("C" & Cell.Row).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next Cell
End Sub

I am trying to apply this set of commands to each cell in the range B7:B50. For each of these cells, I need "cell.Value" to refer to the value in the respective cell and "cell.Row" to represent the row of the cell that is currently being used.

View Replies!   View Related
Way To Set The Printer's Properties Using VBA
Is there a way to set the printer's properties using VBA?

Sometimes we set the printer for BEST quality to do photos or brochures. Well if we don't change it back, when we go to print a spreadsheet, it takes forever because it is set on best quality.

I want to be able to set the print properties to normal using VBA.

View Replies!   View Related
Set Autorecover Save Location In VBA
I have a macro which performs the auto save function perfectly. I'm looking at possible enhancements.

1. How to set autorecover save location in VBA? I know how to do this on the front-end i.e goto tools/options/save tab and set it there As i have disabled save autorecovery feature, autorecover save location is greyed out

2. How to create a backup of my workbook? So that my workbook will perform autosave and when i close this workbook at the end of the day all the latest changes are added to the backup and saved

View Replies!   View Related
VBA Set ODBC Connection String For Query
My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?

The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.

Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).

View Replies!   View Related
Set VBA Range As Cells Selected By Cursor
The VBA code (in the code window) runs nicely on the range B10:B1000, but I'd prefer that it only run on a range I define by the cells that are currently highlighted/selected on the active sheet. How should the line of
Set SHOPS = Range("B10:B1000")

View Replies!   View Related
VBA To Set All Pivot Fields To Default Values
I am finishing up a macro that manipulates a pivot table and creates a file for us to run. But we all keep this pivot table up a lot, and usually keep a lot of the data filtererd in it.

Is there a way to set all of fields in a pivot table back to their default values like when you first open up the workbook?

View Replies!   View Related
Set VBA Variable With Index/Match Array
I created an Index/Match array forumula in the worksheet. It works.

View Replies!   View Related
Vba: Set Print Format Across Multiple Sheets
I'm trying to write a macro to select all the Sheets in a Workbook, and set some properties [Auto ColumnWidths, Landscape, and Fit to 1 page wide] for all of them.
I don't know the names of the sheets, nor how many there will be - this part is tagged on the end of a long macro that creates new files and pastes various data into them. The code below only seems to work on the Active sheet - not any of the others selected. Curiously, I can set a specific column width for all sheets, but not Auto Widths.

With ActiveWorkbook
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End With

ActiveWindow.Zoom = 80 ' This line works!
Selection.RowHeight = 13.5
Selection.ColumnWidth = 34 ' This line works

View Replies!   View Related
VBA Set Protect Sheet - Format Columns And Rows
I have VBA code to set a password on multiple worksheets in a workbook. I new need to force a check in the "Format Columns" and "Format Row" checkboxes. The current VBA code is:

View Replies!   View Related
Set Up A Direct Link To The Internal Calculator, Using A Button In VBA
I'm trying to set up a direct link to the internal excel calculator, using a button in VBA. I have the shortcut on my own toolbar, but I have many users that use the workbook, and to get them all to set up is almost impossible, so I am trying to make it simple for them.

I've tried doing it through a hyperlink direct to the calc.exe file, but get a lot of messages popping up (that'll scare a few of them). So thought I would try and find the excel name for it and use VBA, but I cannot find it anywhere.

View Replies!   View Related
VBA To Look At Tha Last Cell That Is Populated In Column And Set Print Area
I have a template worksheet that several people use and print from.
The range on this worksheet used is A1:C499

I want the VBA to look at tha last cell that is populated in column A and set the print area to suit.
i.e. the last cell used in column A could be A277. then I'd want the print area to be A1:C277.

View Replies!   View Related
VBA Error - Unble To Set The Locked Property Of The Range Class
I am having trouble with a macro for unprotecting a sheet keeping specific cells locked then clearing contents of other cells then reproect the sheet. I wrote one for one sheet which works and I have re used this but it keeps saying Unable to set the Locked property of the Range class.

View Replies!   View Related
VBA - Adding Border Line To Bottom Row Of Set Print Area
I am using the following macro to set print area which I found on the net. I am trying to modify it as I would also like to place a border line on the bottom row but cant find anything to assist me despite searching through several threads.

Dim myrange As String
With ActiveSheet.Range("A:A")
myrange = .Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Address
End With
ActiveSheet.PageSetup.PrintArea = "$K$1:" & myrange
End Sub

View Replies!   View Related
Vba Detect When A Specific Set Of Cells Gets An X Typed Into It And Calculate 1 Cell = 36.50 Hours
I'm working on a timetable for my boss and i'm trying figure out how to have vba detect when a specific set of cells gets an x typed into it and calculate 1 cell = 36.50 hours and input into the appropriate Sum column. The Sum Total is Column AR and i've put x's into some of the cells that will be containing them, i'm sure if i just get the basic code needed i will be able to implement it.

View Replies!   View Related
VBA Code To Set Unknown Range Based On Current Date In 1 Column
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).

View Replies!   View Related
Copy, Paste, Change Font Size, Copy, Paste, Print VBA
I'm using 2003.

1. Copy cells B5 to V-First blank row in Strength Tests worksheet
2. Paste cells into Racks worksheet in cell C5
3. Change font size to 6
4. Sort by Column T descending then by Column C ascending
5. Copy one row (A5-W5 (1Rx23C)) from Racks worksheet
6. Paste row into M1 worksheet in cell D4
7. Print M1
8. Drop down one row on the Racks worksheet
9. Repeat steps 5-8 until there's a blank row.

View Replies!   View Related
Closing Immediate Window
Is there any way to have a macro, that is started from the immediate window, close the immediate window when it's done. My macro closes the immediate window, but as soon as it ends, the immediate window reopens and my cursor is inside it.

View Replies!   View Related
New Window On The Taskbar
i was recently upgraded to Excel 2003 and have found that whenever i open more than one workbook, it opens in a new window on the taskbar (similar to opening another application)... I remember having this in my previous version of excel but i can't for the life of me remember how i switched this off?

View Replies!   View Related
Minimised Window
I open up Excel by double clicking on a file, it opens but it just has a grey background and the file I opened is displayed as a small 1 inch stip in the top left - just a title bar with the windows maximise, minimise and close buttons.

What ever I click I can't see any of the cells!

View Replies!   View Related
Invisible Window
There is a column with a formula and a list validation, and an adjacent hidden column with numbered ids. When moving into the list validation cell the Worksheet_Selection_Change event code saves information about the Target cell - value, address, formula. When a value has been obtained from the list the Worksheet_Change event code looks up a belonging id in a lookup table and put this in the hidden id cell, and puts the saved formula back into the list validation cell. This makes the sheet (or window) unmanagable.

When I move around the sheet I can see the cell name in the Name Box, but the sheet itself is not visible. The only way to display the sheet is to add a new window (Window - New Window), choose the first "frozen" window (Window - 1. window) and quit this window. Then the new window is ready for use, but of course with the same limitations as the newly quitted one. Ring a bell, someone?? The funny thing is that in this workbook I also use the same functionality in another sheet without any problems.

View Replies!   View Related
Active Window
I've built a macro to run a loop that opens excel files, prints certain sheets and closes them. I have the macro working - to some degree. here is my code (this is excel):

Sub RMPProducer()

OldPath = "S:RMBS_Performance_Analytics Analysis1 Staging Folder For Monthly Model Templates2007200704VVDeals"
Dim t As Workbook
Dim s As String
Dim a As Window

With Application.FileSearch
.LookIn = OldPath
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
s = .FoundFiles(i)

I have 2 problems. First problem is that I am trying to add a function to make the window close once its finished printing. This actually works - but it limits my loop iterations from i = 1 to 2 (so it only runs 2 times and I'm not exactly sure why). If I don't include the statement - this can run for up to 7 - 8 times (these are very large files) then excel runs out of memory. Unfortunately, i can't set the loop to run from i = anything other than one.

So What I would prefer is to be able to make the "ActiveWindow.close" command work - because this way I can run the entire loop. But I'm not sure how to. I tried window referencing, I tried adding a new active Window so when the file opens, its opening in the active window already, but nothing works.

Preferably, I need to be able to reference both windows, as my next macro will involve referencing 2 windows (one static - always open) and the other the loop above.

View Replies!   View Related
Copyright 2005-08, All rights reserved