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


Advertisements:










VBA Grab Active Window Index


Dim iStartBook As Long, iNewBook As Long
iStartBook = ActiveWindow.Index
ActiveWindow.NewWindow
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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
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
.NewSearch
.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
Macro To Change Active Window
I'm trying to enter this very simple macro that is supposed to switch from one active window to another. However, I'm getting an error, which I can't quite figure out. Here is the

View Replies!   View Related
Select Previous Active Window
I have multiple excel workbooks open at the same time. I need to run a macro on one of them every 15 min but I need to return to the active window when the macro is done. How do I read what window is active then return to it when the macro is done?

Sub AutoSave()
dTime = Now + TimeValue("00:15:00")
Application .OnTime dTime, "AutoSave"
Windows("data.xlsm").Activate
Sheets("Data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="c:excel" & Format(Time, "hhmmss"), FileFormat:=xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows(1).ActivatePrevious
End Sub

View Replies!   View Related
Make A Cell The Top Left One In The Active Window
I'm trying find a way in VBA to make a particular cell the top left one in the active window.

View Replies!   View Related
Active Cell Location In Window After Hyperlink
I have a worksheet with frozen panes (Row and Column). I have hyperlinks that jump to the this worksheet. Is there anyway to have the window position itself so the active cell is in the upper left corner of my frozen panes?

View Replies!   View Related
Show Text In Active Window Caption
In an open excel file, in the top left-hand corner, the file name is always "Microsoft Excel - Your File Name".

does anyone know how to have the top-left hand corner show "Your File Name" only?

View Replies!   View Related
Index Of Active Worksheet
I've been a somewhat casual Excel user and now need to do some VBA with a workbook that has several worksheets in it. In particular, the workbook has sheets that have been added "out of order", that is, the leftmost sheet is not the first one added to the book.

I need to write a VBA routine that looks "backward" from the current worksheet to "previous" sheets, i.e., sheets to the left of the current worksheet, but not to the right, so I can't refer to them as Sheet1, Sheet2, etc. and they actual display names that are not of that form anyway. I am aware of the "Worksheets" collection and the fact that I can "subscript" that to refer to the sheets in a left-to-right form.

I know that I can refer to the current sheet as "ActiveSheet" and I can determine properties like its name with "ActiveSheet.Name", but is it possible to find its index in the Worksheets collection? I'm looking for a function or property such that "ActiveSheet.Index" will allow me to refer to it as "Worksheets(Index)" and particularly to allow me to refer to preceding sheets as, e.g., "Worksheets(Index-1)".

If worse comes to worst, I can always cycle through the worksheets and check the name of each worksheet like this:

Sub Button1_Click()
Dim index As Integer
Dim strASName As String
strASName = ActiveSheet.Name
index = 0
Do
index = index + 1
Loop Until Worksheets(index).Name = strASName
MsgBox "Index of current worksheet is " & index
End Sub

And while I'm at it, is it possible in Excel to rename a control like "Button1" to something more sensible and mnemonic, like "btnRecalculate" or whatever?

View Replies!   View Related
VLOOKUP Grab The One That Is Listed First
I do have one issue I need to work on with a VLOOKUP formula.

I have a list of cities and counties that the VLOOKUP goes and looks at to pull information from.

The problem is that when there is a city and county with the same name (Example: Boulder and Boulder County) VLOOKUP always seems to jsut grab the one that is listed first in the list.

When you use the city name of Boulder, VLOOKUP always retruns the information for Boulder County.

Here is my forumula:

=VLOOKUP(qtProjAddress3,'City Mileage and Tax Rates'!A2:C518,2)
Is there something I can add to this formula that will allow it to return the proper city or county when the names are similar?

View Replies!   View Related
Grab Data From One Sheet And Put It Into Another
I have two documents with very similar information. Some rows are duplicates. However, there is one column of information missing from the 1st spreadsheet. Let's call them Sheet1 and Sheet2.

How do I tell Excel:
In Sheet1, IF A2 matches any field in Sheet 2, column A

Then: copy the data from the same row, in a different column (Sheet1 A3) and put it into a specific corresponding cell (Sheet2 A3)

Basically I'm trying to grab a product ID from sheet2 and put it in the matching cell, for that specific product. I have all teh product IDs in column A in sheet 2. The product IDs field in Sheet 1 is blank. I need to populate it with teh correct information from Sheet 2. But there are many moreproducts in sheet 2 than in sheet 1, so I can't just sort and do a copy paste. It needs to think "if the product exists in sheet 2, grab the info from sheet 2 and put it into sheet 1 for that product id."

View Replies!   View Related
Grab Sheet Name From A Cell
Im looking to have excel grab the sheet name from the cell rather than manually clicking on the sheet each time. I have attached an example. I would like the formula in cells B5:7 and C5:7 to reference A5:7 rather than the specific sheet as seen in cell B5 --> =b!A1. I assume it will look something along the lines of ='(A5)'!A1, but that doesn't seem to work?

View Replies!   View Related
Grab First Word In Cells
I would like to automate this process that I'm doing currently through the "Text to Columns" function.

Basically I have various cells that contain many worlds such as:

FRESH LUNETTE SOLEIL REES 1'S
INFOKUS SOLEIL LECTURE LUNETTES 1'S
KENSIE LUNETTE SOLEIL ELEORA 1'S
KENSIE LUNETTE SOLEIL ELIZABETH 1'S
KENSIE LUNETTE SOLEIL JAMES 1'S
KENSIE LUNETTE SOLEIL VESPER 1'S
KIDS LUNETTE SOLEIL HOUSTEN 1'S
KIDS LUNETTE SOLEIL MINNIE 1'S

My goal is to separate the first world from the rest of the words in one cell.

Since all of the words are seperated by space, I use the the "Text to Columns" function and separate the words, so that I can get the first word into one column. The rest of the words have to be merged again through "Concatenate".

What I'm looking for is a VBA code or Excel function that can identify the first world (which vary in character lengths) and put it into one column.

View Replies!   View Related
[Tough]VLOOKUP/Other To Validate Then Grab Value!
I have created an image of what i want and hopefully this will explain better.

This is very hard but it is something really important for the company i work for and have an idea on how its done but not enough knowledge to implement yet.


View Replies!   View Related
Grab Data From All Files With Certain Name In A Folder
Is it possible to have a spreadsheet that when opened grabs data from every file with "*invoice" in the title in folder 2009 and all its sub directories and puts them into a list in the opened file?

The data needed is to be from sheet1, cells B16, C18, G18 from all the files
and i would like B16 put into A2, C18 into B2 and G18 into C2
Then the next sheets data B16 to A3, C18 B3 and G18 to C3 etc etc until all files with invoice in the title have been added.

B16 is a name
C18 is an address and
G18 is a postcode

This means there can be no duplicates so if opened twice there will only ever be 1 of the same address and postcode.

View Replies!   View Related
Exiting A Range To Grab Additional Data
I've set a range as DD14 to G014, then using a For Next Loop asked the program to cycle through each cell in the range. Upon findng a populated cell, it stores several bits of data from that cell (already done) and then move up one cell to record the cell value (time) and again move one cell up to record the date.

Now I did get this to work using a clunky bit of code that used activecell.offset to move the focus up the two columns and a While .... Wend loop - but using a set range and cycling through each cell it does not appear to be possible - or am I wrong?

Set MyRange(1) = Range("D14:ER14")
Set MyRange(2) = Range("D23:GN23")
For MyCounter = 1 To 2
For Each Cell In MyRange(MyCounter)
If Cell.Value = "" Then Goto Skip_Cell
MyCell = Cell.Address
MyPosition = InStr(1, Cell.Value, ",")
If MyPosition = 0 And Len(Cell.Value) >= 3 Then
Tool_Number = Left(Cell.Value, 3).....................

View Replies!   View Related
Macro To Open Up Word, Grab A Label Template
Included in the macro, I would like for the macro to open up Word, grab a label template, that I have stored (have to navigate there), and then merge the data from the excel file into the Word template.

View Replies!   View Related
Conditional Summation Formula: Set SUM Code To Grab All Numbers Next To Codes
I am creating a billing sheet that has a column where charges are coded (to determine what they are for) and I want a "Grand Total" for everything followed by a breakdown "Total" for things with the same code. Is there a way to set SUM code to grab all numbers next to codes?? (EX. Column A is Billing Code, column B is Cost in $$; I want a total at the bottom that takes the different amounts in column B based on the various information/codes from column A).

View Replies!   View Related
Find Method To Search For The Active And Non Active Values
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null.

I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it.

I've attached the spreadsheet so you get a better idea of the problem that i encountered.

View Replies!   View Related
Unique List Would Grab All Unique Entries
I have a data table that repeats as follows:

CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC

From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC

I am trying to use the following guide:

http://blogs.techrepublic.com.com/howdoi/?p=111

Get stuck on "Listing A:

View Replies!   View Related
Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately
I have this table

.......A.....B.....C....D
1.....I......a.....d.....g

2.....II.....b.....e.....h

3.....III....c.....f......i

As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i

I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on

I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.

View Replies!   View Related
Find/Left Functions: Grab Everything Left Of The Last Occurrence Of "." In A String
I want to grab everything left of the last occurrence of "." in a string, and in the next cell everything right of the last occurrence of "."

so say the string is 111.111.1.222
column 1
111.111.1

column 2
222

my current code (which works, but its messy) for the first cell is

View Replies!   View Related
Yes/No Pop-up Window
I have a certain macro which copies data from one file to the other. There are several target files (refering to a given month each) and one source file. The user chooses a month from a drop down list in the source file which modifies a certain cell. The value of this cell decides on which of the 12 target files will be opened to paste the data to. The macro also copies the value of a cell which refers to the current date (=TODAY()) This number is then compared with a the range (value between two pints in time: start and end date of a given month). All I need is some sort of a pop-up window with a header i.e. WARNING. and the window text: You are about to send the information to a file which does not refere to the current month, are you sure you want to perform this action" and two buttons YES and NO in case the value of the current date does not match the range set up by the starting and ending of a given month in the target file. The button YES would then do waht ever the code that was placed before the pop-up window said, save the aplication and close it, and the NO button would simply close the aplicatiown without saving it. Something like that:

Sub Export()
'the whole code describing the action to be taken
If Range("A1").Value >= Range("A3").Value And Range("A1").Value <= Range("A2").Value Then
'here the A1 cell refers to the current date and cells A2 and A3 to the start and the end date of the month
End Sub

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
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 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
Tip Messaging (pop-up Like Window Perhaps)
I am using Excel 2003 and wish to make a tip message (pop-up like window perhaps) if a value of a particular cell is exceeded.

View Replies!   View Related
Disable Minimization Of App Window
I am looking for an event that would capture the minimization of the Excel application window so that I can force it to stay maximized.

View Replies!   View Related
Floating Navigation Window
I want to create a floating window in excel that can contain a series of macro buttons (12 total)

I'd like to see if this window can be opened with a hyperlink or a single macro button on a spread sheet.

View Replies!   View Related
Customizing Debug Pop Up Window
If a user decides to hit the esc key right in the middle of one of my procedures (bug free, etc), this will leave the file in an odd state. The file is set up so if the routine is re-run it cleans the file up and restores it to proper form.

But hitting esc exposes my code which is password protected when the file is idle. I employ a little trick that makes it hard to hack into my worksheet code, but one achilles heel is the esc button.

What I want is to have the file close if the routine is stopped in mid stream. This way the code is not exposed, and the user cannot monkey with the file.

I wanted to set the file up where i have a custom debug window that when a button is pressd the file closes OR when the user escapes out of the debug window the file closes as well.


View Replies!   View Related
Cascading Window Tabs - Can You
My spreadsheet has approx 50 tabs, can I cascade them so that I don't have to scroll across all the time to find the one I want (ie, can they all be shown in the one window)

View Replies!   View Related
Macro For Pop-up Window With Picture
A macro, which would allow me to click on a particular cell, which will have an ID number (1 through 34), each ID# is attributed to a particular employee. These ID# are located in my spreadsheet in columns B & G, and the ID #'s begin initially in B6 & G6 and continue downward to B34 & G34. These ID #'s are used in a vlookup type formula which covers 35 pages of this workbook. What I'd like to do is be able to click on any cell between B6& B34 (and/or the same in G6&G34), which has a particular ID # in it and have the photo of that particular employee designated with that ID# pop up in a window generated by the macro. I hope this makes sense! Any help would be greatly appreciated. My 1st sheet in my workbook is my "lookup" tab, which contains my ID# list and numbering sequence affiliated to each employee. So I'm not sure if I'd need to put the photograph of the employee in a column next to the employees name or have a folder designated with all my pictures in a certain directory, etc.

View Replies!   View Related
Check A Particular Window Is Open
Is it possible for excel to check that a particular window is open and if it is then carry on with a macro

View Replies!   View Related
Create A Popup Window
Is it possible to create a popup window that contains drawing objects?
I created a "form" with the objects, but I don't know how to use the form.

I want to have this image, or form, or whatever it is, to popup when the user clicks a cell. Is this possible without getting into programming?

Can it be hidden and then made visible when the cell is selected?

View Replies!   View Related
Automatic Window Sizing
I have the this code in ThisWorkbook to open the workbook with two windows, each with a different tab showing, and each taking up half the screen:

View Replies!   View Related
Open In A Simple Window
Is there a way that I can make an excel sheet and save it in a way that when it's opened the person JUST sees the cells in a really simple window...

Not sure if I'm confusing people in how I explain this... but basically I don't want it to show the scroll bars, the A B C D etc and 1 2 3 4 etc, the tool bars... etc. JUST the specified cells in a sheet.

View Replies!   View Related
Append Value To Url In IE Using Window Handle?
I'm trying to manipulate a URL in an already opened IE browser window. In a nutshell, I need to change "http://www.excelforum.com" to "http://www.excelforum.com/this-is-the-new-part"

The code prior to this does the following:
- Opens a new instance of IE
- Enters the username and password required to get into the site
- Scrapes the source code, imports it into Excel, and parses the data
- Finds a row based on the current date and some text
- Extracts from that row a unique identifier...
- Obtains the window handle ID for the instance of IE displaying the website

Now I need to add the unique identifier to the url. I cannot open it in a new tab or instance of the browser, as it revokes back to the login screen. I was assuming that the best way to do this would be to get the window handle, then just send a "tab" to get back to the URL, and enter the new URL. If there's any other way to do this, I'm all ears. Otherwise, how can I send the keys using the window handle?

View Replies!   View Related
Single Step Window
I do have code, but this thing (which appears on the Excel window, not the VBE window) doesn't seem to be connected to any of my code.

What is this, and what caused it to suddenly pop up on me in Excel?

View Replies!   View Related
Display A Window In Startup
Would anyone be able to tell me how to display a promp at an excel workbook startup so that users agree to the terms and conditions?

View Replies!   View Related
Edit Window Not Working ..
Heres a strange thing, My latest version of a spreadsheet i have design has got a small problem. When clicking on a cell, nothing appears in the edit window, and when you double click on the cell to edit the contents the data disappears.

This only happens on one sheet within a workbook of 7 pages. The werid thing is that it does not happen on the version before

Any ideas how this could of happened and is there a fix?

View Replies!   View Related
Document Recovery Window
When I start Excel I get a Document Recovery Window that lists a file that was lost some time ago. I don't need the file and don't want the window appearing. If I click on the listed file, or right click and select "open", "save as", or "show repairs", the window disappears and I get a blank document.

I know I could write code that would automatically close the window when I start Excel, but there has to be a better way. I DO want the window to appear when there is really a file that needs to be recovered, but I don't want the listed file and don't want the window.

View Replies!   View Related
Force UserForm To Always Be Top Window
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 Replies!   View Related
Activate Application Window
The bold line below does not work, Word remains the active window. The commented routine is a Chip Pearson API routine to do the same thing, it doesn't work either.

If infobox = vbOK Then
'wordapp.Activate
AppActivate "Microsoft Word"
print_dialog = wordapp.Application.Dialogs(wdDialogFilePrint).Show
If print_dialog = 0 Then
AppActivate "Microsoft Excel"
'Call ActivateExcel
End If
End If

View Replies!   View Related
Activate Window Where Name Is In A Cell
I am trying to activate a window and the name of the window to be in a cell. lets say: Windows(file2.xls).Active

I want something like this: The name of the file is in B2 cell in tab Sheet1 in file file1.xls so the line will be like:

Windows(.............).Active
i do not know what function to use.

View Replies!   View Related
Close Browser Window
I'm relatively new to macro's. I have an excel workbook that draws information from an exported file from a website. When I do this I get an IE window open which is of variable name depending on the information I picked on the website. I am trying to create a macro that will close this new window on importing but leave the original website window where it is. The name is variable but has the same format e.g. "http://batch+report+info....." with the later address changing depending on the info selected. I tried using a an " If name.like" formula but am too new to macro's to know how to implement it.

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
Popup Window When You Load Excel To Say Something
I am very new to programming in excel, so bear with me. I need to have a window that pops up when you load excel. I need it to say something like

If A2 > A1, then the popup should say "value exceeded in row 1"
If B2 > B1, then the popup should say "value exceeded in row 2"

If both of the statements above are true, then I would like both of the errors to be in the same window/popup, instead of two seperate ones. I hope thats clear.

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
Open Window With Data From Other Sheet
Say im in sheet 1 at B2. Am looking to create a button so when I press it a small window opens and displays the data that is it sheet 2 (which is just 2 columns of a list of products and price.
Then I for example double click on a product and it will be added in the current cell.


View Replies!   View Related
How To (not) Save Workbook Window Position?
I have two wide screens, and I stretch excel over both of them to gain the most useable space. Sometimes I save a file while it's on the right screen. This causes problems for my boss, because she opens the file on her computer and it opens offscreen way to the right. I could have sworn there was a setting in excel to save or not save the workbook's window position, but now I can't find it.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved