Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficult but supposing not imposible
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficult but supposing not imposible
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. -- HTH... Jim Thomlinson "filo666" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficult but supposing not imposible
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. -- Regards, Tom Ogilvy "filo666" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficult but supposing not imposible
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 "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "filo666" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficult but supposing not imposible
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficult but supposing not imposible
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 -- HTH... Jim Thomlinson "filo666" wrote: 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 "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "filo666" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficult but supposing not imposible
Hi Mr(s?) Filo666 (How I hate these stupid nicknames!!) I can't imagine that you would have to manipulate ALL of thes applications settings, but I agree with you that on occassions yo would like to store the original value and then reset them on exit. I this is important for you get a copy of Bullen/Bovey/Green ' "Professional Excel Development" (THE best book I have ever read o building Excel applications and beyond that on programming in general and read the chapter on Dictator Apps. Jim: "Application settings should only be modified ...." I don' agree, read the same chapter. Tom: "Hopefully your users won't approach management to have you fired Good luck" Come on! Not all people posting in this newsgroup have you level of experience; bare with them, just share your (immense!! knowledge with kind regards, Ton Teun -- Ton ----------------------------------------------------------------------- TonT's Profile: http://www.officehelp.in/member.php?userid=4 View this thread: http://www.officehelp.in/showthread.php?t=66475 Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help, too difficult for me. | Excel Worksheet Functions | |||
Too difficult for me, please help. | Excel Worksheet Functions | |||
Difficult but do-able? | Excel Discussion (Misc queries) | |||
A difficult question | Excel Programming | |||
Is this imposible for VBA to perform? | Excel Programming |