Just curious why you presume user has automatic Calculation...

Dim lCalcMode&

With Application
lCalcMode = .Calculation: .Calculation = xlCalculationManual
'...
End With

'...code

With Application
.Calculation = lCalcMode
'...
End With

...when it's easy to preserve/restore user setting!

Another issue is when more than one procedure is coded to toggle common
settings. I use a central handler routine that ensures only one process
has control...


'--------------------------------------------------------------------------------------
' **Note: EnableFastCode requires the following declarations be in a
standard module.
'--------------------------------------------------------------------------------------
'Type udtAppModes
' 'Default types
' Events As Boolean: CalcMode As XlCalculation: Display As Boolean:
CallerID As String
' 'Project-specific types
'End Type
'Public AppMode As udtAppModes
'--------------------------------------------------------------------------------------
Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
' **Note: Requires 'Type udtAppModes' and 'Public AppMode As
udtAppModes' declarations

'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID < Caller Then _
If AppMode.CallerID < "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
AppMode.CalcMode = .Calculation: .Calculation =
xlCalculationManual
AppMode.Events = .EnableEvents: .EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub

...where Caller is defined in the process controlling these settings
like so...

Sub MyAction()
Const sSource$ = "MyAction"

EnableFastCode sSource
'...code

EnableFastCode sSource, False
End Sub

...so if the above routine calls other procedures that also toggle these
settings, they can't interfere with the original caller's control. The
other procedures may be used independantly and so may need to toggle
settings if not already 'in play' by some other proc.!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion