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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
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. "gab1972" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
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 "gab1972" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
Hi Peter, I believe you are referring to code 1004. Err 400 is regarding
showing forms modal that are already visible. "Peter T" wrote: 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 "gab1972" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
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 "JLGWhiz" wrote in message ... Hi Peter, I believe you are referring to code 1004. Err 400 is regarding showing forms modal that are already visible. "Peter T" wrote: 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 "gab1972" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
@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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
@Peter
Do I put this in my coding? On Error Resume Next Err.Raise 400 MsgBox Err.Description |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
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 "gab1972" wrote in message ... @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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
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" wrote in message ... @Peter Do I put this in my coding? On Error Resume Next Err.Raise 400 MsgBox Err.Description |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
What do you mean by this?
"Also move your module level variables into the normal module." |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
Did you read my reply to your previous post ?
Regards, Peter T "gab1972" wrote in message ... What do you mean by this? "Also move your module level variables into the normal module." |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
yeah, sorry...just went back through and checked.
how do you "step through" your coding? I really appreciate all your help! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
"gab1972" wrote in message:
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
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 "JLGWhiz" wrote in message ... Hi Peter, I believe you are referring to code 1004. Err 400 is regarding showing forms modal that are already visible. "Peter T" wrote: 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird error code..."400"
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 "Peter T" <peter_t@discussions wrote in message ... 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 "JLGWhiz" wrote in message ... Hi Peter, I believe you are referring to code 1004. Err 400 is regarding showing forms modal that are already visible. "Peter T" wrote: 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 |
Reply |
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 |