| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Don't know what is happening.... | Excel Discussion (Misc queries) | |||
| not sure what's happening | Excel Programming | |||
| Why this is happening | Charts and Charting in Excel | |||
| Why is this happening? | Excel Worksheet Functions | |||
| what's the Bug....tell me what's happening??? | Excel Programming | |||
Linear Mode
