personal.xls : hide with XLApp statement ???

[Don]

I've got the following code in personal.xls (ThisWorkbook) to set calculation
to automatic and set iterations to true. Code works great...but I'd like to
insert some additional code to make personal.xls hide itself. I was thinking
perhaps another XLApp statement added somewhere below...but I haven't a clue.
Any suggestions? Thanks.

Private WithEvents XLApp As Excel.Application

Private Sub Workbook_Open()
Set XLApp = Excel.Application
End Sub

Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)
XLApp.Calculation = xlCalculationAutomatic
XLApp.Iteration = True
XLApp.MaxIterations = 9999
End Sub

Private Sub XLApp_WorkbookOpen(ByVal Wb As Workbook)
XLApp.Calculation = xlCalculationAutomatic
XLApp.Iteration = True
XLApp.MaxIterations = 9999
End Sub

 

[Dave Peterson]

Personally, I wouldn't bother using code.

Open excel (and personal.xls).
With personal.xls the activeworkbook
Windows|hide
Close excel. You'll be prompted to save personal.xls. Make sure you click yes.

Restart excel and test it out.

 

[Don]

When I do what you suggest, I get a debug error next time I start up a
worksheet. So it seems to "break" the way the code works. When I
subsequently unhide the worksheet (personal.xls) and save it, close excel
then reopen with some file other than personal.xls, everything works fine.
If I can't hide it with code, that's fine, but I'd like to hide it if
possible, and for some reason, the solution below breaks the functionality.
Thanks for the suggestion.

 

[Dave Peterson]

Ahh.

You're right.

The problem is that there has to be an active/visible workbook to change the
calculation mode.

This may work better (not vigorously tested):

Option Explicit
Private WithEvents XLApp As Excel.Application
Private Sub Workbook_Open()
Set XLApp = Excel.Application
End Sub
Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)
ChangeTheCalculationMode
End Sub
Private Sub XLApp_WorkbookOpen(ByVal Wb As Workbook)
ChangeTheCalculationMode
End Sub
Sub ChangeTheCalculationMode()
Dim TempWkbk As Workbook
If ActiveWorkbook Is Nothing Then
Application.EnableEvents = False
Set TempWkbk = Workbooks.Add(1)
Application.EnableEvents = True
End If
XLApp.Calculation = xlCalculationAutomatic
XLApp.Iteration = True
XLApp.MaxIterations = 9999
If TempWkbk Is Nothing Then
'do nothing
Else
TempWkbk.Close savechanges:=False
End If
End Sub

This seemed to work ok in xl2003.

 

[Dave Peterson]

The OP wants to put the code in personal.xls and he also wants that workbook
hidden.