Weird error code..."400"

[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. 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"), _
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

 

[JLGWhiz]

Here is the explanation for error code 400:

You can't use the Show method to display a visible form as modal. This error
has the following cause and solution:

You tried to use Show, with the style argument set to 1 – vbModal, on an
already visible form.
Use either the Unload statement or the Hide method on the form before trying
to show it as a modal form.


I don't see the offending command line in the code you posted but I am sure
it is in there. You might try stepping through the code to find the specific
line that initiates the error.

 

[Peter T]

Err 400 is a generic "Application-defined or object-defined error". In
context the description may be more informative.

Sounds like the error is in your OnTime macro "Auto_Close"macro which you
didn't post. Just step through that macro.

Regards,
Peter T

 

[JLGWhiz]

Hi Peter, I believe you are referring to code 1004. Err 400 is regarding
showing forms modal that are already visible.

 

[Peter T]

Hi JLG,

I haven't tried but I don't doubt 400 is raised in the scenario you mention.
However I would assume the same error might be raised in other situations.
But I could be wrong!

On Error Resume Next
Err.Raise 400
MsgBox Err.Description

Maybe the OP will advise after s/he has debugged and reported back.

Regards,
Peter T

 

[gab1972]

@Peter
I did post the Auto_Close...it's in there.

Forgive my stupidity...i don't know that i've used something to "step
thru" my coding. I've always just inserted msgboxes with "stop" in
coding to step through. Is there an easier way?

Also, what Show form is conflicting? I don't see any show form in my
coding, other than my little splash screen, but that disappears after
you click on it or close it.

 

[gab1972]

@Peter

Do I put this in my coding?

 

[Peter T]

I did post the Auto_Close...it's in there.

Ah so you did, miss

There's quite a lot wrong. Not sure what you are trying to do with the
OnTime every 15 min, code in two close events (not that the Auto_Close will
fire in the wb module).

Start by doing what I suggested in your post of 23 Jan subject "ThisWorkbook
coding problem..."
Also move your module level variables into the normal module.

Regards,
Peter T

 

[Peter T]

No!
It's only to see if error code 400 generates a specific error message (other
than "Application-defined or object-defined error")

Regards,
Peter T

 

[gab1972]

What do you mean by this?

"Also move your module level variables into the normal module."

 

[Peter T]

Did you read my reply to your previous post ?

Regards,
Peter T

 

[gab1972]

yeah, sorry...just went back through and checked.

how do you "step through" your coding?

I really appreciate all your help!

 

[Peter T]

yeah, sorry...just went back through and checked.

It often happens people post a question, then forget to check if there's
been an answer (sometimes a detailed one), then ask similar a few days
later.

how do you "step through" your coding?

Put the cursor in the routine, then press F8 repeatedly. Press F5 to run
normally.

If you click in the vertical bar next to some line of code you'll insert a
'break' indicated by a brown dot in the vertical bar. If you run normally
(or F5) the code will halt at the break, from there you can F8 to step
through, then say F5 to run to the next break or until the code terminates.

(There's more you can do but that should be enough for your immediate needs)

Regards,
Peter T

 

[Peter T]

Here's one way to generate a 400 error without a description

- A toolbar button to call a macro in a workbook (eg in Personal)
- Have the wb loaded in one Excel instance but not in a second instance
- In the instance without the wb, press the button to call the macro
- A warning msg will display asking if you want to open the wb as Read-only
(being already open in the other instance)
- Press cancel
- Error 400 !

Regards,
Peter T

 

[Peter T]

Maybe it's something similar with the OP.
Haven't tried to follow the code but perhaps the Ontime is trying to call a
macro that was found (otherwise the can't find macro error) but the workbook
then closes and before the macro can run. Just a guess.

Regards,
Peter T