XL: Application.Interactive Value Not Reset When Macro Stops

ID: Q151332


The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition


SYMPTOMS

Application.Interactive does not reset itself to a default value after the end of the macro is reached.


CAUSE

The Interactive property of the Application object allows a macro to block all user input from both the keyboard and the mouse. The property is useful in situations where user input has the potential to disrupt macro operations. However, unlike most of the Read/Write properties of the Microsoft Excel Application object, Application.Interactive does not reset itself to a default value after the end of the macro is reached. Because of this, if the interactive property is not reset back to a true value in the code of the macro, or an error occurs during execution of the macro, Microsoft Excel will not respond to user input even though the macro has technically stopped executing.


RESOLUTION

If you forget to reset the property back to true, or encounter an error in the execution of the code, you will likely need to shut down that instance of Microsoft Excel and fix the line of code that caused the error.

To do this, press CTRL+ALT+DELETE in Windows 95/98 or Windows NT and end the Microsoft Excel task.

On the Apple Macintosh, press OPTION+COMMAND+ESCAPE to force Microsoft Excel to quit.

Under Windows 3.x you may be able to press CTRL+ALT+DELETE, but more often than not, because no "error" has occurred, you will have to restart the machine.

Sample Visual Basic Procedure

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft Support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/overview/overview.asp
If it is necessary to enable user input to Microsoft Excel without restarting the program, you can reset the property using Automation through a Visual Basic for Applications macro.

NOTE: The success of the following macro depends on the ability to open a separate instance of Microsoft Excel. Under Windows 3.x, the number and complexity of programs that can be running concurrently is directly dependent on available system resources. If the instance of Microsoft Excel that is not responding, or the instance you are trying to open contains many worksheets, charts and so on, system resources will be significantly reduced. In Microsoft Excel 98, you cannot start a separate instance of Microsoft Excel, and therefore must run the macro from another program.

The method for opening a separate instance of Microsoft Excel varies depending on the operating system.

Windows 3.x and Windows NT

  1. Press ALT+TAB to switch to Program Manager.


  2. Either double-click the Microsoft Excel icon or click Run on the File menu. Type the complete path to the Microsoft Excel executable file, for example, c:\Excel\Excel.exe, and then press ENTER.


Windows 95

  1. Click Start, and then click Run


  2. Type the full path to Microsoft Excel in the Open box, for example, c:\Excel\Excel.exe, and then press ENTER.


  3. In Microsoft Excel 97, point to Macro on the Tools menu, and then click Visual Basic Editor.


Microsoft Excel 5.0 on the Apple Macintosh

  1. Switch to Finder.

    NOTE: If you have Microsoft Office Manager installed from Microsoft Office and you have QuickSwitch enabled for the Microsoft Office Manager, you can press COMMAND+TAB to switch to Finder. Otherwise, you have to click the Finder desktop or another program window in the background to switch to that program.


  2. Double-click the hard disk drive and locate the Microsoft Excel icon.


  3. Create a copy of the Microsoft Excel icon by holding down OPTION button and dragging the icon to the desktop.


  4. Double-click the Microsoft Excel icon.


After starting a separate instance of Microsoft Excel using one of the methods listed earlier, follow these steps:

  1. On the Insert menu, click Module.


  2. Type the following code in the Module sheet.
    
          Sub reset_interactive()
    
             Dim xlobj As Object
    
             ' The GetObject method will access the open instance of Microsoft
             ' Excel, not a new one.
             Set xlobj = GetObject("Book1")
    
             ' Where book1 is the name of the Workbook that has stopped
             ' responding to user input. Make sure to leave the extension,
             ' .xls, .xlt, and so on, off.
             ' Because the above line assigns the first Worksheet of book1 to
             ' xlobj, we must access the Application property of the object in
             ' order to reset the Interactive property.
    
             xlobj.Application.Interactive = True
    
          End Sub 


  3. To run the macro, click the insertion point in the macro code, and then press F5.


Microsoft Excel 98 Macintosh Edition

As stated previously, you cannot start more than one instance of Microsoft Excel 98; therefore, you must run the Visual Basic for Applications Sub procedure from another Microsoft Office program. In this example, we will use Microsoft Word 98.
  1. Switch to Microsoft Word 98.

    NOTE: If you have Microsoft Office Manager installed from Microsoft Office and you have QuickSwitch enabled for the Microsoft Office Manager, you can press COMMAND+TAB to switch to Finder. Otherwise, you would have to click the Finder desktop or another program window in the background to switch to that program.


  2. On the Tools menu, point to Macro, and then click Visual Basic Editor.


  3. On the Insert menu, click Module.


  4. Type the following code in the Module sheet:
    
          Sub reset_interactive()
    
             Dim xlobj As Object
    
             ' The GetObject method will access the open instance of Microsoft
             ' Excel.
             Set xlobj = GetObject(,"Excel.Application")
    
             ' Sets the Interactive property for the Excel application back to
             ' True.
             xlobj.Interactive = True
    
          End Sub 


  5. To run the macro, click the insertion point in the macro code, and then press F5.



STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.


REFERENCES

For more information about the Interactive property in Microsoft Excel 97, from the Visual Basic Editor, click the Office Assistant, type interactive, click Search, and then click to view "Interactive Property."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:

Q176476 OFF: Office Assistant Not Answering Visual Basic Questions
"Microsoft Excel 5 Visual Basic for Applications Reference", version 5.0, page 342

"Microsoft Excel/Visual Basic Reference", second edition, page 394

Additional query words: 5.00a 5.00c

Keywords : kbole kbprg kbdta kbdtacode PgmOthr KbVBA
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,97; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbprb


Last Reviewed: November 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.