Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Module for setting Automatic Calc
I use Excel 2000 at work and as you are aware there is a bug with "view
all windows in taskbar" option for shared workbooks. It gets turned off when you open a shared workbook. I have fixed the bug with the help of this newsgroup, by inserting a class module, which resets it to "on" My question is, the same problem occurs with calculation option, it gets set to manual. Can I reset it to automatic with a class module, or by some other means, and if so, how? TIA Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Module for setting Automatic Calc
I've never noticed a bug with the calculation setting.
Excel determines the calculation from the first workbook that's opened in that session. I think I'd try this first.... Option Explicit Sub auto_open() Dim wkbk As Workbook Set wkbk = Workbooks.Add Application.Calculation = xlCalculationAutomatic 'wkbk.close savechanges:=false End Sub But if you close that temporary workbook, I think (I don't recall for sure) that excel will pick up the calculation mode from the next workbook opened--if there isn't an activeworkbook. If you feel like experimenting, uncomment that .close line and open a workbook that was saved with calculation set to manual. If xl changes calculation mode back to manual, then don't use that "wkbk.close" line. ========= But if you really think it's some other thing changing the calculation mode (can you post what you do and when it changes that setting), you could use an application event like this: Inside the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Application Private Sub Workbook_BeforeClose(Cancel As Boolean) Set xlApp = Nothing End Sub Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) If Application.Calculation < xlCalculationAutomatic Then Application.Calculation = xlCalculationAutomatic Beep 'just so you're aware that it changed. End If End Sub It actually checks the calculation setting each time you select a different range. GregR wrote: I use Excel 2000 at work and as you are aware there is a bug with "view all windows in taskbar" option for shared workbooks. It gets turned off when you open a shared workbook. I have fixed the bug with the help of this newsgroup, by inserting a class module, which resets it to "on" My question is, the same problem occurs with calculation option, it gets set to manual. Can I reset it to automatic with a class module, or by some other means, and if so, how? TIA Greg -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Module for setting Automatic Calc
Dave, I think what happens is, if the workbook is opened by more than
one individual at the same time, the calculation gets set to manual. That is only a problem for me when I run a macro that copies a sheet from the workbook and pastes the values into a new workbook. If calculations have not occured, the new workbook has errors. I've taken your suggestion and added a line to my code which forces calculations just before the copy function. I am assuming that the line: Application.Calculation = xlCalculationAutomatic will force a calculations. Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Module for setting Automatic Calc
Application.CalculateFull
forces calculation. -- Regards, Tom Ogilvy "GregR" wrote in message oups.com... Dave, I think what happens is, if the workbook is opened by more than one individual at the same time, the calculation gets set to manual. That is only a problem for me when I run a macro that copies a sheet from the workbook and pastes the values into a new workbook. If calculations have not occured, the new workbook has errors. I've taken your suggestion and added a line to my code which forces calculations just before the copy function. I am assuming that the line: Application.Calculation = xlCalculationAutomatic will force a calculations. Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Module for setting Automatic Calc
Thanks Tom
Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Module for setting Automatic Calc
I've never seen anyone post this kind of problem (and I never used shared
workbooks in real life). But maybe someone else is opening the workbook, toggling the calculation to manual, saving the workbook and the other person opens that workbook as the first workbook resulting in manual calculation setting. (But I think you're much better off adding the protection for your macro into the code of your macro!) GregR wrote: Dave, I think what happens is, if the workbook is opened by more than one individual at the same time, the calculation gets set to manual. That is only a problem for me when I run a macro that copies a sheet from the workbook and pastes the values into a new workbook. If calculations have not occured, the new workbook has errors. I've taken your suggestion and added a line to my code which forces calculations just before the copy function. I am assuming that the line: Application.Calculation = xlCalculationAutomatic will force a calculations. Greg -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Module for setting Automatic Calc
I agree. I, also am not intimateley familiar with shared workbooks.
I'll keep an eye and see if I can figure it out. Thanks for your input. Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CLASS MODULE & SIMPLE MODULE | Excel Discussion (Misc queries) | |||
Class module in VBA | Excel Programming | |||
Class module | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |