LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 57
Default ThisWorkbook coding problem...

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"), _
procedu="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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
ThisWorkbook Problem Paige Excel Programming 4 November 14th 07 10:00 PM
Problem with ThisWorkbook.RefreshAll Vick Excel Discussion (Misc queries) 3 September 7th 07 12:26 AM
ThisWorkBook problem Blogd_Node Excel Programming 3 April 26th 06 07:19 AM
Set Range on ThisWorkBook problem. Cesar Zapata Excel Programming 1 January 31st 06 10:35 PM


All times are GMT +1. The time now is 10:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"