Save and Restore Excel Workbook Options using VBA

[smiller]

I have a spreadsheet that requires changing the Excel Workbook options. When the spreadsheet is opened the options for this Workbook replace the Excel options that the user has set. I am trying to find the proper VBA procedure to save the user's Option settings prior to the Workbook being opened so the user settings can be restored when my Workbook is closed. Suggestions appreciated.

 

[hedrijw]

Asuming the settings are the ones accessible from Tools Options, which ones do you want to save? There are a lot of them

 

[smiller]

I want to save all of the setting on the Tools>Option area that the user has before my workbook is opened. When the user is finished with my workbook I want to restore the settings that the user had before using my workbook. Does this make sense?

 

[hedrijw]

It makes perfect sense. As far as I know, you have to save each of the settings one at a time. Say in case of the View option you want to save the current Formula Bar and Status bar visiblity propertys:

formbar = Application.DisplayFormulaBar
statbar = Application.DisplayStatusBar

Then when you restored at the end,

With Application
.DisplayFormulaBar = formbar
.DisplayStatusBar = statbar
End With

You can find the name for each option by using the record macro feature

 

[smiller]

Thank you very much for your prompt response. I was hoping that each individual setting would not have to be addressed.