How can I prevent this from happening?

[Conan Kelly]

Hello all,

I have the following code in a class module 'EventClass' in my
personal macro workbook so it will run each time a new file is opened:





Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
If Application.Calculation = xlCalculationAutomatic Then
' Application.Calculation = xlCalculationManual
' Application.CalculateBeforeSave = False
Application.CommandBars("My Toolbar").Controls(2).Caption =
"AutoCalc On"
Application.CommandBars("My Toolbar").Controls("AutoCalc
On").TooltipText = "Turns AutoCalc Off"
' MsgBox "AutoCalc is now OFF.", vbInformation, "AutoCalc
Status"
Else
' Application.Calculation = xlCalculationAutomatic
' Application.CalculateBeforeSave = True
Application.CommandBars("My Toolbar").Controls(2).Caption =
"AutoCalc Off"
Application.CommandBars("My Toolbar").Controls("AutoCalc
Off").TooltipText = "Turns AutoCalc On"
' MsgBox "AutoCalc is now ON.", vbInformation, "AutoCalc
Status"
End If

End Sub




The problem I am having is if I open Excel normally (standard Excel
shortcut in start menu etc. with a new blank workbook), I get no
errors. But if I open Excel by dbl clickin an Excel file, I get a
'Type mismatch' error. Also, I get this same error if I open Excel
with the /e switch (no splash screen & no workbook opened--this is the
preferred way, I'd like to put this in my 'Startup' folder in my Start
menu).

Is there a way I can modify this so I won't get the error when opening
Excel the two problematic ways described above?

Thanks for any help anyone can provide,

Conan Kelly

 

[Dave Peterson]

Untested....

If you close all your workbooks, but leave excel open, you'll notice that you
can't change the calculation mode.

Excel wants a workbook open/visible when you do this.

Maybe you could add a little bit to create a new workbook to your code:


dim tempWkbk as workbook

application.enableevents = false
set tempwkbk = workbooks.add
application.enableevents = true

'do your stuff

tempwkbk.close savechanges:=false

 

[Conan Kelly]

Dave,

Thanks for the info. That sounds like it might work.

But after you mentioned that you cant change the calc mode w/o any
visible workbooks open, it got me thinking.

Couldn't I wrap an IF statement around my code checking for open,
visible workbooks? If so, how would I check for the open, visible
workbooks?

Thanks again,

Conan

 

[Dave Peterson]

Dim myWindow As Window
Set myWindow = ActiveWindow

If myWindow Is Nothing Then
'no activewindow
Else
'do your stuff
end if

I didn't get an error if there were no windows active. But if you do:

Dim myWindow As Window

set myWindow = nothing
on error resume next
Set myWindow = ActiveWindow
on error goto 0

If myWindow Is Nothing Then
'no activewindow
Else
'do your stuff
end if