You are here:

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
End Sub

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
End Sub

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?

Hi Mike,

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.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Damon Ostrander


I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.


I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 All rights reserved.