How can I prevent this from happening?
 

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

How can I prevent this from happening?
 

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 wrote:

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

How can I prevent this from happening?
 

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" wrote in message
...
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 wrote:

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

How can I prevent this from happening?
 

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

Conan Kelly wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson