Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Message: "Can't Execute Code in Break Mode" | Excel Programming | |||
"Document not saved" error after typing a line of code in Excel VB | Excel Programming | |||
OnTime code error "can't execute code in break mode" | Excel Programming | |||
"File Format Not Valid" When Starting Excel. Error Code 0D3F6000 | Excel Discussion (Misc queries) | |||
What is Error "Method "Paste" of object "_Worksheet" failed? | Excel Programming |