Excel/Keep current Excel 2010 settings from affecting next sheet opened
QUESTION: Good afternoon,
I have created an Excel Timer in Office 2010, in which I use VBA to hide the bars, etc. and to reduce the window size and position it at the bottom right of my screen.
The problem I am running into, is that if there is currently an Excel sheet open, it reduces both sheets to the size of my timer. If the timer is open, and I open another worksheet, the 2nd sheet opens into the same size, bars, etc. as my timer.
Can this be avoided?
I would like to keep the timer running and allow the end user to open and close other sheets without resizing, etc.
ANSWER: Hi Mike,
I apologize for my slow response to your question.
Based on your problem description there are many things that I can interpret in multiple ways, so if I make the wrong assumptions please feel free to follow up and correct my assumptions and I will try to answer the right question.
Firstly I assume that you are using the Application.OnTime method to implement your timer, and it calls a procedure that reduces the window size using the ThisWorkbook.Windows(10.Width= and .Height= properties to set the window (process) size on the screen.
Secondly, you mention opening another worksheet, and I assume that you are not doing this manually, but that the same procedure that sizes the window also opens another worksheet. Further, I assume you really mean worksheet (i.e., a Worksheet object) rather than a workbook (a Workbook object). This means that you only have one Excel process running, and that your workbook contains multiple worksheets and you either manually open second worksheet or the procedure the timer runs opens a second worksheet.
Here is how to get it to automatically size the window up when the second sheet activates, and automatically size it back down when the timer sheet re-activates:
In the second sheet's event code module paste this code:
Private Sub Worksheet_Activate()
ThisWorkbook.Windows(1).Width = 1000
ThisWorkbook.Windows(1).Height = 800
To open the second sheet's event code module simply right-click on the sheet's tab, select View Code, and paste this code into the empty code module that appears.
To automatically size the window back down when the timer sheet re-activates put the following code in the timer sheet's event code module:
Private Sub Worksheet_Activate()
ThisWorkbook.Windows(1).Width = 100
ThisWorkbook.Windows(1).Height = 100
Note that I just picked arbitrary sizes (in pixels) for the windows in each case.
I hope you find this helpful.
---------- FOLLOW-UP ----------
No worries on the time frame. I appreciate the help.
Yes, I am using the Application.OnTime Method to make the timer run. I call the timer spreadsheet from a link to that excel file and use Auto Open to run the macros that use a With Application and set the window height, width, top and left values to re-size and position my window. It also runs the macro that uses ActiveWindow.Display to hide the Headings, HorizontalScrollBar, VerticalScrollBar,WorkbookTabs, FormulaBar, Gridlines and StatusBar.
The problem is that if I already have a workbook open (any other workbook) and I open the timer spreadsheet / workbook (with a single worksheet) from the link, it causes the timer workbook as well as the original workbook that is already open, to re-size and hide bars, etc.
If I have the timer workbook running / open and need to open another workbook (not a worksheet in the same workbook) the new workbook opens to the same size and hidden bars as the timer workbook.
For instance, I have a spreadsheet open from a Sharepoint site for tracking sales. I then need the timer. I click the link for the Timer spreadsheet and it opens, re-sizes, hides bars and relocated. When I go back to my Sales tracking spreadsheet, it too has been re-sized, bars hidden, etc.
Vice versa, if I have the timer spreadsheet open and sitting on the desktop to use and I need to open a different spreadsheet for my Sales Tracking or any other Excel sheet for that matter, the new spreadsheet takes the same attributes as the timer spreadsheet.
This is what I am attempting to prevent. I need the timer spreadsheet re-sized so it takes up as little desktop real estate as possible, but I need it to not affect the attributes of any other spreadsheet that is either already open or subsequently opened after the timer spreadsheet is open.
If I convert the timer spreadsheet to an EXE file, that works, as it appears to open the Timer in it's own instance of Excel, independent from all other spreadsheets that are or do get opened. However, the other users that I am building this for do not have the ability to run this program as an executable on their restricted machines, so that is not a viable option.
Sorry for the vaugeness of the original message. Does that clear the issue a bit?
Again I apologize for taking so long. I realized some time ago that a solution would be to open a new Excel process from VBA, but it just occured to me that Automation provides this capability (I should have thought of this solution before now). I just tried it an it appears to work. So here is how to do it.
In your code where you now have the statement that opens the other workbook do this instead:
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
' create a new instance of the Excel application
Set oXL = CreateObject("Excel.Application")
' open your desired workbook (replace my file name and path with yours)
Set oWB = oXL.Workbooks.Open("C:\Users\Damon\SkyDrive\Documents\Damon\WB1.xlsx")
' make it visible
oXL.Visible = True
' set the position and size of the new Excel app window
oXL.ActiveWindow.Width = 800
oXL.ActiveWindow.Height = 500
oXL.ActiveWindow.Left = 200
oXL.ActiveWindow.Top = 50
At this point you can interact with the file manually or continue with VBA. You can quit this
instance of Excel manually or from VBA
I trust this will accomplish what you want, but if any problems feel free to follow up.