Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
ThisWorkbook Problem | Excel Programming | |||
Problem with ThisWorkbook.RefreshAll | Excel Discussion (Misc queries) | |||
ThisWorkBook problem | Excel Programming | |||
Set Range on ThisWorkBook problem. | Excel Programming |