Is a visible workbook open?

  • Thread starter Thread starter pk
  • Start date Start date
P

pk

Hello, please help on this...

I have an ending subroutine that resets a number of
application elements and is frequently called by any
number of other subroutines.

The only one that causes a problem is the following:

Application.Calculation = xlCalculationAutomatic

I frequently turn calculation off to speed up the code. In
some cases when the code ends, there may be no files open,
in which case the calculation line generates an error.

I really want to keep this code line in the ending
subroutine the way it is structured now. I need your
example code (one line if at all possible) to determine if
any files are open. For example, I tried this, but of
course it doesn't work:

If Not IsEmpty(ActiveWorkBook.Name) Then _
Application.Calculation = xlCalculationAutomatic

I would like something on this order if possible. Ideas?

Your example code most appreciated. Thanks in advance.
 
Presumably you are running your macro from a hidden workbook such as Personal.xls. You could detect how many workbooks are open
with:

Workbooks.Count

If only one is open, it is your hidden workbook and you should not run the code..

If Workbooks.Count > 1 Then
Application.Calculation = xlCalculationAutomatic
End If

If the situation is more complex, you could simply avoid the error by the following

On Error Resume Next
Application.Calculation = xlCalculationAutomatic
On Error GoTo 0
 
Back
Top