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 Weird error code..."400"

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. I posted
this before, but didn't get an answer that helped me...so I'm hoping
to get better luck this time.

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
Error Message: "Can't Execute Code in Break Mode" cru Excel Programming 6 November 21st 08 11:41 PM
"Document not saved" error after typing a line of code in Excel VB JP Excel Programming 0 July 27th 07 01:24 AM
OnTime code error "can't execute code in break mode" tskogstrom Excel Programming 1 September 8th 06 10:29 AM
"File Format Not Valid" When Starting Excel. Error Code 0D3F6000 EMT_Hawk Excel Discussion (Misc queries) 4 March 31st 06 09:22 PM
What is Error "Method "Paste" of object "_Worksheet" failed? vat Excel Programming 7 February 17th 06 08:05 PM


All times are GMT +1. The time now is 02:45 AM.

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

About Us

"It's about Microsoft Excel"