ThisWorkbook coding problem...

[gab1972]

Okay, so I'm adding some features to my workbook that will:
1. turn off all menu bars, headings, etc.
and
2. auto close after inactivity

I think there is a conflict in my coding somewhere.

When i open the book, the menu bar stuff disappears like it
should...but after my testing 15second lapse time, I get a MS VBA
error box that just says "400". Usually when I get an error it's
syntax or the like...this box just has the number 400 in it.

Can someone look at my coding below and shed some light? Thanks.

Option Base 1
Private Changed As Boolean
Dim MoveAfterReturn As Boolean
Dim MoveAfterReturnDirection As XlDirection
Dim CBvisible() As Boolean

Private Sub Workbook_Open()

Dim i As Integer

'Hide all commandbars, including formula bar, but not Worksheet Menu
Bar

With Application

ReDim CBvisible(.CommandBars.Count)

For i = 1 To .CommandBars.Count
CBvisible(i) = .CommandBars(i).Visible 'save original
visibility state
If .CommandBars(i).Name <> "Worksheet Menu Bar" Then
If .CommandBars(i).Visible Then .CommandBars(i).Visible =
False
End If
Next i

.DisplayFormulaBar = False

With .CommandBars("Worksheet Menu Bar")
For i = 1 To .Controls.Count
Select Case .Controls(i).Caption
Case "&File", "&Help"
Case Else
.Controls(i).Visible = False
End Select
Next i
End With

'save current settings so they can be restored later,
'then set enter key to move down
MoveAfterReturn = Application.MoveAfterReturn
MoveAfterReturnDirection = Application.MoveAfterReturnDirection
.MoveAfterReturn = True
.MoveAfterReturnDirection = xlToRight
End With

'Turn off row and column headings
ActiveWindow.DisplayHeadings = False
Sheets("Lists").Range("I2").Value = ""
Sheets("Home").Select
PermitTrackerSplash.Show

'coding for closing inactive book
Changed = False
Application.OnTime Now + TimeValue("00:00:15"), _
procedure:="ThisWorkbook.Auto_Close"

End Sub

'additional coding for closing inactive book
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
Changed = True
End Sub

'additional coding for closing inactive book
Private Sub Auto_Close()
If Changed = False Then
'ThisWorkbook.Close SaveChanges:=True <---- if I use this line
instead of the next line, it closes okay, but if I make any changes to
the book and end up on another sheet, it busts
Application.Run "ThisWorkbook!Workbook_BeforeClose" <---- i'm trying
to make it go to my sub that turns back on all my buttons

End If
Changed = False
Call Application.OnTime(Now + TimeValue("00:00:15"),
"ThisWorkbook.Auto_Close", , False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'turns everything back on before closing
Dim i As Integer

'Unhide all commandbars, including formula bar, but not Worksheet
Menu Bar
With Application
For i = 1 To .CommandBars.Count
If .CommandBars(i).Visible <> CBvisible(i) Then
.CommandBars(i).Visible = CBvisible(i)
End If
Next i

.DisplayFormulaBar = True

With .CommandBars("Worksheet Menu Bar")
For i = 1 To .Controls.Count
.Controls(i).Visible = True
Next i
End With

'restore move-after-enter original settings
.MoveAfterReturn = MoveAfterReturn
.MoveAfterReturnDirection = MoveAfterReturnDirection
End With

'Turn on row and column headings
ActiveWindow.DisplayHeadings = True

End Sub

 

[Peter T]

I've only skimmed a few parts of your code but suggest you move everything
that's currently in each of your workbook event procedures into new
procedures in a normal module, and call each proc from the appropriate event
wb event. Ie each event will have just one line Call procWBOpen().

Change your OnTime's to run the new procs in the normal module.

In passing 'Sub Auto_Close()' would normally reside in a normal module to
fire more or less in the same way as Workbook_BeforeClose()

Suggest save a backup of your xlb file, just in case you close the last
instance of Excel without doing the restore for some reason.

Regards,
Peter T