Ensure at least one workbook open

[Ken Puls]

Hey guys,

Just ran into an issue trying to run my code to restore a user's environment settings to what they were before I started my macro. Code is here:

Option Explicit
Dim xlCalcState As Long

Sub Environ_RestoreSettings()
'Macro created 06/11/2005 22:33 by Ken Puls
'Macro Purpose: To restore application properties to user settings
'Restore screen updates, clear statusbar and set
'calculation back to user's initial setting
With Application
    .ScreenUpdating = True
    .DisplayAlerts = False
    .Calculation = xlCalcState
    .StatusBar = False
End With
'Set the calculation state variable to 0
xlCalcState = 0
End Sub

The code fails on the line that sets the calculation back if there isn't at least one workbook showing in the Excel window. Anyone know some quick code to drop in there to ensure at least one workbook is open? I'd do it, but I don't have time to look for it right now.

Thanks!

[Ken Puls]

Nevermind!

Had a brain wave:

Sub Environ_RestoreSettings()
'Macro created 06/11/2005 22:33 by Ken Puls
'Macro Purpose: To restore application properties to user settings
Dim wb As Workbook
'Make sure that there is a workbook showing, or calculation code will fail
Set wb = ActiveWorkbook
If wb Is Nothing Then Set wb = Workbooks.Add
'Restore screen updates, clear statusbar and set
'calculation back to user's initial setting
With Application
    .ScreenUpdating = True
    .DisplayAlerts = False
    .Calculation = xlCalcState
    .StatusBar = False
End With
'Set the calculation state variable to 0
xlCalcState = 0
End Sub

Thanks!

[Bob Phillips]

Ken,

You could try Workbooks.Count > 0. Problem is that if you have a hidden workbook such as Personal.xls then that will count, and I don't know if that will throw your code. An alternative is Windows.Count > 1.

[Zack Barresse]

And don't dim wb, just use a one-liner ..

If ActiveWorkbook Is Nothing Then Workbooks.Add

Set your wb in that same line only if you will need to work with it.

[Ken Puls]

Sweet!

Thanks to both. Code already deployed, but I'll fix it up going forward.

That's what being in a hurry does to you, y'know?