XL: Saving and Restoring Workspace Settings

ID: Q99367

The information in this article applies to:

SUMMARY

In Microsoft Excel, the GET.WORKSPACE() function returns information about the workspace settings and can be used to determine if a certain workspace setting is enabled or disabled. However, if you are running a macro that changes the settings in the Workspace Options dialog box, and you would like to restore the settings to their original values at the end of the macro, using the GET.WORKSPACE() function is not the best method to use-- there is no argument in the GET.WORKSPACE() function that will test the status of the Info Window, Note Indicator, or Command Underline (Macintosh Only).

In Microsoft Excel version 4.0, you can use a workbook to restore the original workspace settings. To restore all of the workspace settings, create a workbook at the beginning of the macro, and then close the workbook and reopen it at the end of the macro. In Microsoft Excel, version 3.0, use a workspace file.

Note that in Microsoft Excel version 5.0, in a Visual Basic macro, you can store any view, edit or general settings (settings available in the Options dialog box) at the beginning of your macro, and then restore the values at the end of your macro.


MORE INFORMATION

When a workbook file is opened in Microsoft Excel version 4.0, the current settings in the Workspace Options dialog box are replaced with the settings saved in the workbook. Even though the workbook is then immediately closed, the workspace settings remain. In Microsoft Excel version 3.0, these settings are saved in a workspace file.

To restore the workspace settings after you run your macro, use the appropriate procedure below.

In Microsoft Excel 4.0

All of the settings in the Workspace Options dialog box are saved in a workbook file. Follow these steps in your macro (or do them manually):

  1. From the File menu, choose New from the File to create a workbook. Then save and close the workbook.

    Note: If you are using a macro, you will probably want to make this step an Auto_Open macro so that it will run automatically each time you open the macro sheet.


  2. Change the workspace settings and run your macros.


  3. Open the workbook that was created in step 1 and then close it (or delete the workbook).

    Note: If you are using a macro, you will probably want to include this step as an Auto_Close macro so it will run automatically when you close the macro sheet.


In Microsoft Excel 3.0

All of the settings in the Workspace Options dialog box are saved in a workspace file with the exception of the Alternate Menu choice, available in Microsoft Excel for Windows. To determine this setting, use GET.WORKSPACE(28) in your macro, which returns TRUE if Microsoft Menus are selected. Follow these general steps in your macro.

  1. Create a workspace file by choosing Save Workspace from the File menu. Or, define a variable to store the GET.WORKSPACE(28) value.

    Note: If you are using a macro, you will probably want to make this step an Auto_Open macro so that it will run automatically each time you open the macro sheet.


  2. Change the workspace settings and run your macros.


  3. Open the workspace file that was created in step 1. Or delete the workspace file and restore the Alternate Menu choice using the WORKSPACE() command and the variable defined in step 1.

    Note: If you are using a macro, you will probably want to include this step as an Auto_Close macro, so that it will run automatically when you close the macro sheet.



REFERENCES

"User's Guide", version 3.0, page 550
"User's Guide 1", version 4.0, pages 67-79

Additional query words: 4.00a 


Keywords          : kbenv 
Version           : WINDOWS:3.0,4.0,4.0a; OS/2:3.0; MACINTOSH:3.0,4.0
Platform          : MACINTOSH OS/2 WINDOWS 
Issue type        :

Last Reviewed: April 5, 1999