Activate Inactive Workbook
Aug 2, 2009
I am having a "cosmetic" issue that I was curious if I can fix it.. I have a workbook that opens a network workbook, saves some data to it, and then closes the network workbook.
Everything is working fine, except I cant get the code to "reactivate" the initial workbook. After excel saves and closes the 2nd workbook I opened, my screen stays on my desktop, instead of refocusing on the initial workbook. I have to manually click on the initial workbook in my taskbar to bring it back into focus...
I thought the below code would bring the inital workbook back into "focus" on the users screen, but it's not working.
Windows("NEWRightFit.xls").Activate
Sheets("Sheet1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
Windows("sxssubmit.xls").Activate
Confirmation.Show
End Sub
I just moved the Application.ScreenUpdating line as initially I had it at the very bottom, but that did not correct. The confirmation.show is just a dialog box that informs the user the changes were saved successfully. I would like the first workbook "sxssubmit.xls" to come back into focus automatically though, without the user having to manually select it from the taskbar.
What is my code missing?
View 9 Replies
ADVERTISEMENT
Jun 8, 2014
I open two spreadsheets only. In one of them I would like the filename of the other spreadsheet shown in cell A1.
The reason why is the filename is a 6 number date and I'd like to confirm it showing in full format.
View 3 Replies
View Related
Apr 23, 2008
I have two or more DIFFERENT INSTANCES of excel workbooks open at the same time. EX: Wk1, Wk2, & Wk3
Currently I have Wk3 showing in the screen.
Through a Macro, how can I display (bring to front) Wk1 without closing Wk3 ?
This is part of a longer macro, so I only need to know how to do above.
View 15 Replies
View Related
Sep 16, 2009
I have an open workbook(A.xls) where the user can press a button which opens another workbook (B.xls) In workbook B they need to add new names then press another button to run another script. The script needs to switch to workbook A in order to work correctly. How can I switch to workbook A without using the name of the workbook? The reason I cant use the name to activate is because the first workbook that is open is not always A.xls
View 4 Replies
View Related
Oct 3, 2013
How do I use VBA to Activate Sheet1 of a workbook? I am using the following to go BACK a sheet, but really need to get to the first sheet in the workbook.
Code:
Sheets(Sheets.Count - 1).Select
If it's a new wb, then it's tab is called Sheet1.If it's an existing wb, and it only has 1 sheet, then I need that one (in case someone has added a sheet and deleted Sheet1)
View 3 Replies
View Related
Aug 15, 2008
I have 2 workbooks open
Book1 has a call to ChangeBook()
Book2 is empty
Function ChangeBook()
Application.Workbooks("Book2.xls").Activate
End Function
ChangeBook() is located in a .xla file loaded on Excel launch.
Calling the function from Book1 does not cause Book2 to activate.
Running the function via the F5 key in the IDE works fine.
I am modifying existing routines that have problems and being able to activate certain books at will would make the changes much easier.
View 9 Replies
View Related
Apr 18, 2009
-I've been trying to get an answer to this problem for some time and I would like to try again from a different approach.
I may have 5 workbooks open in 5 different Instances of Excel. The number of files open varies. When the below Function is called, it checks to see if the file, trying to be opened, is already open or not.
Currently, if the file is already open, a mesage is displayed stating that it is open and the user has to search through tabs looking for the file so that it can be displayed.
What I want to do in place of a message, is activate the file that is already open and display it. No message necessary.
I have tried "Application.Activate" and can not make it work.
View 14 Replies
View Related
Jul 22, 2009
I have .xls files (ex: a.xls, b.xls etc) in a shared drive.How do I get information (run macros without changing anything) from a.xls and create a new file in my hard drive without opening a.xls. I don’t know if that is possible to do. May be sounds very weird. For example, I name this file on my hard drive as a01.xls, b01.xls from b.xls. Now I run other macros from a01.xls. which is active. I want this macros to run independent of the file name the user creates. In otherwords the workbook has to be variable. Not sure how to use ‘Thisworkbook’ function if that is what is needed to do.
View 12 Replies
View Related
Jan 26, 2010
On a command button in a userform, I have this code to open another workbook:
View 2 Replies
View Related
Jul 9, 2012
i need a VBA code to activate workbook (which is already opened) with reference to name in cell A1
in Cell A1 is "masterworksheet.xls"
View 4 Replies
View Related
Nov 5, 2008
I have a workbook containing macros that opens a second one (no macros) and does some 'data mining'.
The following VBA is used for that (I use this code on dozen of files and it has always worked, except here):
Set WshShell = CreateObject("WScript.Shell")
ChDir (WshShell.SpecialFolders("MyDocuments"))
BladNaam = Application.GetOpenFilename("Excel File, *.xls", , "Excel")
Workbooks.Open FileName:=BladNaam
TabNaam = ActiveSheet.Name
The problem is that most of the time the newly opened workbook is NOT activated (i.e put on top) and thus the rest of the code is executed on the wrong workbook...
If I put in a Msgbox(ActiveWorkbook.Name) 9 out of 10 times the active workbook is the initial one and not the one that was opened by the code.
tried replacing Active.Workbook with wb (dim wb as workbook).
tried to wait-a-few-seconds in between code
tried renaming
View 9 Replies
View Related
Oct 20, 2007
I wrote code to update some workbooks. The code opens the workbooks and then activates the workbook to add the update.
I was tired when I wrote the code to activate the workbook and it is written:
Workbooks("Update").Activate
The updates have already been sent out and it is not working on some computers. (If I change the code to
Workbooks("Update.xls").Activate
it works fine.)
Is there some option in the VBA editor that I can have people change on their computer so the code will run? Why does it work on some computers and not others?
(Unfortunately, rewriting the code to add ".xls" and resending everything isn't an option.)
View 7 Replies
View Related
Mar 19, 2008
button on main workbook opens 2 other workbooks and assigns a workbook object to them. the 2 opened workbooks are Activated in turn, range values changed and macros on these sheets invoked and results captured and pasted back onto the starter workbook. the macro is within a sub in a module as are the ones in the second workbook. An example of the code used is:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Dim wkbTest As Workbook
Set wkbTest = Workbooks("Test.xls")
wkbTest.Activate
Sheets("G").Activate
Range("Today").Value = Format(Now(), "dd-mmm-yy")
Application.Run "'" & wkbTest.Name & "'" & "!TestMacro" ............
View 5 Replies
View Related
Dec 11, 2008
I use a workbook where new worksheet tabs are added and removed daily. Every day I use the second-to-last tab (2nd from the right) and the last tab (far right). What code would enable me to activate and reference both of these tabs individually? I believe it would be something like the code below but I can't figure it out...
View 5 Replies
View Related
Mar 19, 2012
I am writing code in Excel VBA and trying to control power-point. at the end there is message-box pop up. Everything runs fine.
But at the end I have go bottom windows bar and click on the excel file then message box comes up, otherwise excel tag keeps blinking at the bottom. I want to add something to code so message box in excel file is pop-up automatically without me clicking on excel file.
View 1 Replies
View Related
Aug 20, 2008
activate an open workbook based on the workbook name that is typed into a cell that is a named range?
So, I keep wanting to do this:
Windows.Activate Filename:=Range("MyRange")
Because this works:
Workbooks.Open Filename:=Range("MyRange")
View 9 Replies
View Related
May 18, 2009
I have a workbook where the sheets are all protected and I want to stop users scrolling up or across beyond the limits of the input areas.
I have used the following code (or variations of)...
Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:G32"
End Sub
This works fine except that when I first open the workbook, the first sheet displayed can still be scrolled. As soon as you move to another sheet and then back again, it is then OK.
It appears that the Worksheet_Activate code does not execute when the workbook is first opened.
How do I get round this?
View 9 Replies
View Related
Feb 24, 2009
I have a program that has all Excel Workbooks in seperate instances of Excel. There is a very sound reason for doing this.
The user has maybe 3 to 10 workbooks open. There are times when a workbook is active and has a Macro Link to open one of the already open workbooks.
When the user clicks the link, they naturally get an error message stating that the workbook is already open. Then they have to close the error msg and click on the Macrosoft Tab and look thru the list of open workbooks and then click the one they are looking for. Additionally, in this Menu Program the user really doesn't even have to know the name of the various workbooks.
I hope everyone will believe me when I state that this program works berautifully.
Right now I am simply cleaning up and making a few little things work better.
QUESTION When the user clicks on a macro link that is to open a workbook that is already open, how - On error - can I have the macro continue on and activate the requested workbook - - - Please remember they are all in separate instances of Excel.
Since the code I'm using can determine if the requested workbook is already open, I think there has to be a way to activate that workbook.
View 14 Replies
View Related
Dec 10, 2009
I am having trouble referencing a range in an inactive worksheet to feed an vba array. this is the following vba code that does not work:
View 4 Replies
View Related
Oct 6, 2009
i have a workbook that contained a single sheet, i ciopied this sheet and amended them accordingly (ie picking up different variables etc).
For some reason every now and again some of the cells on the sheets that are effectively copies of the original dont update, i literally have to click on them and hit Enter.
I know that calculation is switched on as automatic.
View 9 Replies
View Related
Jun 8, 2006
I added a combo box to each sheet of a workbook. Each box referenced a range within the sheet and linked to a cell on the sheet. Each box was tested and worked perfectly. Yesterday. On opening the book today, however, I've discovered each combo box is dead in the water. They are totally inactive and can't be activated in any way. I've checked the properties sheet for each and can't see anything out sorts. As a test, I added a new combo box to a sheet, and interestingly enough, it not only works fine, but also seems to have activated the original box, which also now works fine. Other boxes on the other sheets are still inactive, however.
View 5 Replies
View Related
Sep 21, 2009
close the inactive file
I'm using the
View 4 Replies
View Related
Apr 16, 2014
I use the 'on time' function to kick users out of a shared workbook after 30 minutes of inactivity (i.e. no cells updated). Technically it works, as it does kick out the users.
However the time after which it does so seems to bear no relation to the time I have instructed. Sometimes shorter, sometimes longer! If it is was always longer, I would be happy that there is an error in my code somewhere (i.e. the time value is resetting on an unexpected event that I need to find and alter).
View 6 Replies
View Related
Aug 8, 2009
I need to grab data from one sheet while I'm in a different one - same workbook, however. From the first sheet, the data that I need would be an "activeCell.value" issue. From the other sheet, however, how can I get that cell's data - an "inactiveCell.value" call, in essence? Right now, I'm getting around it by assigning the value to a global variable but that seems wasteful.
View 4 Replies
View Related
Sep 17, 2009
I want to create ranges in a worksheet that is inactive.I have 3 sheets in the worksheet and i want, while the first sheet remains active, to create ranges in the second one.
I'm using the following code that creates no errors no matter how i use it but it works only if the second sheet is active.
View 10 Replies
View Related
Dec 15, 2009
In VBA, is there code to clear all filters from an inactive worksheet or does the worksheet need to be activated?
View 14 Replies
View Related
Dec 18, 2008
CopyPaste operations leaves the goal range selected and I don't want that. The .Activate and .Select methods seem to require that the cell to be selected refers to the ActiveSheet.
What I'm doing now to change the active-cell is kind of:
1. Disable ScreenUpdating
2. Save a reference to ActiveCell
3. Activate the relevant goal-worksheet
4. Change the ActiveCell to the upper-left-cell of the Selection
5. Come back by the reference in point number 2
6. Enable ScreenUpdating.
It works, but seems to me as a huge job for something as simple than "collapsing" a multi-cell-selection. Another irritating problem is the slight screen-shaking caused by the disabling/enabling ScreenUpdating.
View 9 Replies
View Related
Sep 7, 2009
I tried to use Workbooks("Inactivebookname.xlsm").Close False
and many variations thereof, but I simply cannot close this workbook when currently another workbook is active.
I have no problem in closing an active workbook with
ActiveWorkbook.Close
View 9 Replies
View Related
Nov 11, 2009
I'm just trying to figure out how to implment UDFs. I got a nice tool that provides linear interpolation for a data table. Unfortunately after saving, closing and opening the file the cells only show #NAME? in it. Do I have to activate macros or something like that?
View 9 Replies
View Related
Jun 30, 2007
I am using PrimoPDF, as suggested elsewhere in the forum, to attach a PDF to an E:Mail and send it automatically, it works a treat ... however, I do have to manually intervene to click on the OK button & then again to click on YES when the filename is duplicate. I would like to automate these actions and am looking for a way to do it. It looked like the SendKeys command was perfect, but I'm not 100% sure how to do it. The PrimoPDF window is open, named as "PrimoPDF", but is not my active window, so I have put PrimoPDF.SendKeys "{Enter}"
... into the program but I am getting the error message "Run Time Error '424': Object required". Is this something to do with my code ? I am guessing that the program isn't recognising that PrimoPDF is an open & inactive window, so am I missing a prefix here or is it something more serious, like having to add a new Component or Additional Controls ?
View 3 Replies
View Related
