difficult but supposing not imposible

G

Guest

Hi, I'm tryng to accomplish something:
there is somehow save the excells aplication options before start to run a
program, disable them and when your programs ends enable them again.

For example:
I don't want in my program that the automatic calculation is disabled, so in
a auto_open() sub I wrote application.Calculation = xlAutomatic, then ina a
auto_close() sub I put manual calculation.
The problem is that I have 30 users, and they are very angry with me because
each time they use my program in his computer all excells is badly
configurated.
there is some way to save the actual excells state.
look the code attached so you cand understand the problemtatic
my program works just if in the auto_open() sub appears the following code:

Sub customized()
With Application
.CellDragAndDrop = False
.FixedDecimal = False
.FixedDecimalPlaces = 2
.AskToUpdateLinks = False
.MoveAfterReturnDirection = xlToRight
.EnableAutoComplete = False
.EnableAnimations = False
.DisplayPasteOptions = False
.DisplayInsertOptions = False
.ReferenceStyle = xlA1
.IgnoreRemoteRequests = False
.PromptForSummaryInfo = False
.DisplayRecentFiles = False
.StandardFont = "Arial"
.StandardFontSize = "10"
.RecentFiles.Maximum = 0
.EnableSound = False
.RollZoom = False
.DisplayFunctionToolTips = False
.MapPaperSize = True
.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = False
.AutoRecover.Enabled = False
.ShowStartupDialog = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
.DisplayCommentIndicator = 0
.ShowWindowsInTaskbar = False
.Calculation = xlAutomatic
With ActiveWindow
.DisplayFormulas = False
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
ActiveSheet.DisplayAutomaticPageBreaks = False
ActiveWorkbook.DisplayDrawingObjects = xlAll

End Sub

TIA
 
G

Guest

Application settings should only be modified in procedures that start and
finish while the user is locked out of the sheets. You can use them with
click events or change events, but you should not use them with open or close
events. Otherwise you are creating side effects (unwanted changes that are
not a part of the functionallity of the called procedures). The idea is to
make your modifications and then immediately clean up after yourself so the
user is non the wiser as to what you did.
 
T

Tom Ogilvy

wouldn't common sense tell you to store the current values and restore them
when you exit. If you can't think of how to store them, how about on a
hidden sheet. Also, it is unlikely that you would need to touch most of
those settings at all. The best guidance is to leave things alone unless
you absolutely must alter them. Just because you recorded this code,
doesn't mean you need to keep all the settings. If you only need to alter
one setting, then remove everything but that one setting.

Hopefully your users won't approach management to have you fired. Good
luck.
 
G

Guest

I understand what you mean, but imagin this:
user A have in his computer the manual calculation
user B have in his computer the automatic calculation

My program needs the manual calculation "off" (automatic calculation) so,
when user A runs my program, my program will enable automatic calculation,
and when the user finishes to use my program the automatic calculation will
be disabled and nothing changes

BUT

for user B, when he runs my program, my program will enable automatic
calculation, and when the user finishes to use my program the automatic
calculation will be disabled, DISABLED, and user B had automatic calculation
enabled before my program runs.
and this is with just one application type, what about the other 40 and the
30 different computers that uses my program, there are a very huge number of
options.
so, how to save the actual state of excel????
TKS
 
N

Nick Hebb

Sounds like a high maintenance group. You could add a worksheet with a
Range of user names and settings. Then the auto_open () could check
the Application.UserName versus the names in the Range and change the
settings accordingly.
 
G

Guest

That is a tough audience. Your code needs to be more robust. The situation
you describe will still cause difficulties when the user has your program
open and switches to his own spreadsheet. I still say avoid the on open. At
the click or change or whatever check the current calculation state. If it
does not need to be changed then all is well and you can follow one set of
actions directly. If the setting is incorrect then you need to modify it, run
your macro and then reset it. Something like this...

Sub test()
If Application.Calculation = xlCalculationAutomatic Then
MsgBox "All is well"
'Call MainMethod
Else
Application.Calculation = xlCalculationAutomatic
MsgBox "All is well"
'Call MainMethod
Application.Calculation = xlCalculationManual
End If
End Sub
 
Top