PDA

View Full Version : [SOLVED] Save Application Current States. Recall them when needed. (Rescue Workbook)



MrRhodes2004
11-01-2016, 04:19 AM
Hey Group,

It has been a while for me to post here and I have always received excellent help.

I am in the process of writing a "tool" that significantly manipulates application and workbook settings. I am using Full Screen and removing all standard Excel options then providing my own buttons for navigation. For the most part, it will works well (enough). The coding is ugly as sin though.

I am about to send the tool out for alpha testing. The first couple of user crashes left the application in the modified state. There was literally nothing available to the user except the worksheet with gridlines. Since the user have no clue about excel, they were completely stuck. Lucky I was there to be able to reset what had been done and develop a fix. But that was just one user.

I am looking for a way to use a new, blank workbook that records all of the states of excel. For example, below are some of the states that I would like to capture and save to the workbook:

ActiveSheet.Calculate
ActiveWindow.DisplayHeadings
ActiveWindow.DisplayGridlines
ActiveWindow.DisplayHorizontalScrollBar
ActiveWindow.View
ActiveWindow.Zoom
ActiveWindow.DisplayWorkbookTabs
Application.DisplayFormulaBar
Application.ScreenUpdating
Application.WindowState


The idea is that the user would open this RescueWorkbook.xlsm prior to working with my "tool". It would record their settings..."just in case". If the tool caused a problem, they could reopen the RescueWorkbook and it could reset all of the original values.

Is there an easy way to do this? I also don't want to miss elements of the application that are inadvertently or unintentionally changed.

Thoughts? Ideas?

Michael

PS. Are there brave souls who would be willing to test the "Technical" aspects of the "tool"? No understanding of the material required but be able to read and following instructions, click buttons, try to do different things, essentially try to break the tool so that I can attempt to fix the "bugs" before it goes live? It is for my master thesis and I defend in January.... and I would rather it not "Break" during the defense.

snb
11-01-2016, 04:44 AM
Avoid any adaptation of application or workbook settings.
Use a userform that hides the application. Do all the reading and writing in VBA.
All users will be happy.

MrRhodes2004
11-01-2016, 04:57 AM
Thank you snb for the suggestion but I am already 20+ sheets and a dozen userforms into the project. I have already created the problem and pretty deep with them. Now, I just to be careful about testing and catching errors to mitigate the situation. Unfortunately, I am too far in to go back because I do not have enough time to redevelop.

Paul_Hossler
11-01-2016, 05:46 AM
I would not over complicate things


In your workbook, add a macro sheet with Option Private Module so that the macros do not show on Run, but still can be run by entering the name

Add three macros, one to configure the UI the way you want to use for the application ('User'), and one to reset it to normal ('Restore'), and one that calls both just in case ('Reset')

In Workbook_Open run the 'User' macro to configure the UI and then launch your application

In Workbook_Close run the 'Restore' macro to make sure that Excel is in a known state for the next time

If something breaks, the use can use Alt-F8 and enter 'Restore'



Obviously, the BEST thing to do is to catch errors, handle them, and then either make a graceful exit or continue

The attachment is a (very) quick example of the concept to give some ideas

I use the Option Private Module macro as a easy way to get into 'development' mode/configuration and to return to 'production' mode since you have to know the 'secret' macro name to run it

MrRhodes2004
11-02-2016, 08:01 AM
I would not over complicate things
Obviously, the BEST thing to do is to catch errors, handle them, and then either make a graceful exit or continue


Thank you Paul for the suggestions. As life goes, the project started as a straightforward set of sheets. As requests came in and concepts changed, it escalated quickly into complicated. I would love to elegantly catch all of the possible errors before "breaking" excel or putting it into a locked continuous loop. I'm sure we all have been there. I program at an intermediate level at best. My code is rarely elegant nor efficient due to lack of experience and training.

The initial question still remains; how do I collect all of the user machine's environment states?

Paul_Hossler
11-02-2016, 02:37 PM
These go into the application workbook, and not a separate 'Rescue' WB


In the ThisWorkbook module



Option Explicit

Private Sub Workbook_Activate()
User
End Sub

Private Sub Workbook_Deactivate()
Restore
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Restore
ThisWorkbook.Saved=True
End Sub

Private Sub Workbook_Open()
'save
initCalc = Application.Calculation
initDispHead = ActiveWindow.DisplayHeadings
initDispGrid = ActiveWindow.DisplayGridlines
initDispHorz = ActiveWindow.DisplayHorizontalScrollBar
initView = ActiveWindow.View
initZoom = ActiveWindow.Zoom
initDispTabs = ActiveWindow.DisplayWorkbookTabs
initDispForm = Application.DisplayFormulaBar
initScrnUpd = Application.ScreenUpdating
initWindState = Application.WindowState

User
End Sub




The Open saves the settings in variables, and then configures for the User

The Close returns the setting to what they were

The Activate and Deactivate do similar if you were to switch to another workbook temporarily



In a standard module




Option Explicit
Option Private Module

Public initCalc As XlCalculation
Public initDispHead As Boolean
Public initDispGrid As Boolean
Public initDispHorz As Boolean
Public initView As XlWindowView
Public initZoom As Variant
Public initDispTabs As Boolean
Public initDispForm As Boolean
Public initScrnUpd As Boolean
Public initWindState As XlWindowState

Sub User()
Application.Calculation = xlCalculationManual
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.View = xlNormalView
ActiveWindow.Zoom = 100
ActiveWindow.DisplayWorkbookTabs = False
Application.DisplayFormulaBar = False
Application.ScreenUpdating = False
Application.WindowState = xlMaximized
End Sub

Sub Restore()

Application.Calculation = initCalc
ActiveWindow.DisplayHeadings = initDispHead
ActiveWindow.DisplayGridlines = initDispGrid
ActiveWindow.DisplayHorizontalScrollBar = initDispHorz
ActiveWindow.View = initView
ActiveWindow.Zoom = initZoom
ActiveWindow.DisplayWorkbookTabs = initDispTabs
Application.DisplayFormulaBar = initDispForm
Application.ScreenUpdating = initScrnUpd
Application.WindowState = initWindState
End Sub

Sub Reset()
Application.Calculation = xlCalculationAutomatic
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.View = xlNormalView
ActiveWindow.Zoom = 100
ActiveWindow.DisplayWorkbookTabs = True
Application.DisplayFormulaBar = True
Application.ScreenUpdating = True
Application.WindowState = xlMaximized
End Sub



The Public variables hold the initial status

The Sub User configures the application, and the Sub Restore puts things back to the way they were when the WB was opened. Sub Reset just forces a more normal group of settings

MrRhodes2004
11-03-2016, 12:36 AM
Thank you