Error 1004 occurs only in specific cases

[Udo]

Hi experts,

a simple macro that is executed on the start of Excel generates an - at
least for me - puzzling effect. The macro is part of a workbook which
is in the folder XLStart, so it will be opened every time Excel is
started. When I start Excel directly it works perfectly well without
any problem. Whenever I start Excel indirectly by opening any other
workbook, then I get the error 1004, specifying that Excel can't
execute the first line of the macro ("Method 'Calculation' of the
object 'Application' does not work").
These are the first lines of the macro:

With Application
.Calculation = xlCalculationManual
.MaxChange = 0.001
End With
Call CreateMenu
Windows("Personl.xls").Visible = False
ThisWorkbook.Sheets("Menu_Blatt").Visible =
xlVeryHidden

Do you have any hints how to prevent this effect (except never starting
Excel indirectly... ;-) )?
Thanks in advance
Udo

 

[Dave Peterson]

There are some settings that need to have an activeworkbook to change.

If you close all the workbooks with visible windows and try to do it manually,
then you'll see the same problem.

dim tempwkbk as workbook
set tempwkbk = nothing
if activeworkbook is nothing then
set tempwkbk = workbooks.add
end if
'change the setting

if tempwkbk is nothing then
'do nothing
else
tempwkbk.close savechanges:=false
end if

ps.

Did you spell personal.xls correctly?

 

[Udo]

Hi Dave,

thanks for the response.
I entered your suggested code before the existing part. The error
message is still exactly the same. Or did I get you wrong? Can you give
more details?

Thank you in advance
Udo

 

[Dave Peterson]

I think you'll have to share what you did.

Hi Dave,

thanks for the response.
I entered your suggested code before the existing part. The error
message is still exactly the same. Or did I get you wrong? Can you give
more details?

Thank you in advance
Udo