Thanks. I tried that first but received a compile error, expected "=" instead
of "is". Replaced "is" with "=" and cleared error but still did not shut down
Excel when the workbook is closed.
"jaf" wrote:
> Carl,
> Set oCtrl = Nothing should be Set oCtrl is Nothing.
>
> John
>
>
> "Carl" <(E-Mail Removed)> wrote in message news:520C3A7E-8068-425A-9569-(E-Mail Removed)...
> > Thanks for your help, jaf.
> >
> > The code is the only code in my test file just to eliminate any question. I
> > entered the following and it still did not close Excel:
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Dim oCtrl As Office.CommandBarControl
> > For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
> > oCtrl.Enabled = True
> > Next oCtrl
> > Application.DisplayFullScreen = False
> > Set oCtrl = Nothing
> > Application.Quit
> > End Sub
> >
> > Thanks again. Please keep those ideas coming. Carl
> >
> > "jaf" wrote:
> >
> >> Hi Carl,
> >> That was the only code in the workbook?
> >>
> >> I'm not using 2007. Try this one line change. I know the ribbon & control bars went thru design changes in XL 2007.
> >> It could be a bug. Or as MS call them "by design".
> >>
> >> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >> Dim oCtrl As Office.CommandBarControl
> >> For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
> >> oCtrl.Enabled = True
> >> Next oCtrl
> >> Application.DisplayFullScreen = False
> >> SET OCTRL IS NOTHING
> >> Application.Quit
> >> End Sub
> >>
> >> John
> >>
> >>
> >>
> >> "Carl" <(E-Mail Removed)> wrote in message news:6C5251DC-B9AA-49D3-A3B7-(E-Mail Removed)...
> >> >I eliminated that possibility by creating a brand-new workbook as a
> >> > macro-enabled 2007 file and entering the following code:
> >> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >> > Dim oCtrl As Office.CommandBarControl
> >> > For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
> >> > oCtrl.Enabled = True
> >> > Next oCtrl
> >> > Application.Quit
> >> > End Sub
> >> > When I close the file it closes but Excel remains open. I need Excel to
> >> > close so it will stop timed events from reopening the file the next time the
> >> > event is due to start. Thanks for taking the time to help. By the way, the
> >> > same code works perfectly in office 2003.
> >> >
> >> >
> >> > "jaf" wrote:
> >> >
> >> >> Hi Carl,
> >> >> You probably have an object that is still being used by the system.
> >> >> Do you have a routine to set objects you created to nothing?
> >> >>
> >> >> set objXL = nothing
> >> >>
> >> >> John
> >> >>
> >> >>
> >> >> "Carl" <(E-Mail Removed)> wrote in message news:4F113CB7-22A1-481E-8AC9-(E-Mail Removed)...
> >> >> > Thanks for the suggestion but I tried that and it did not work. My operating
> >> >> > system is Vista and I am using Office 2007. I also tried "Application.Quit"
> >> >> > twice. Any other ideas? Thanks.
> >> >> >
> >> >> > "Jacob Skaria" wrote:
> >> >> >
> >> >> >> Try saving the workbook..
> >> >> >>
> >> >> >> ThisWorkbook.Save
> >> >> >> Application.Quit
> >> >> >>
> >> >> >> If this post helps click Yes
> >> >> >> ---------------
> >> >> >> Jacob Skaria
> >> >> >>
> >> >> >>
> >> >> >> "Carl" wrote:
> >> >> >>
> >> >> >> > I have used the following code to shut down Excel 2003 when the workbook is
> >> >> >> > closed but now that I am using Excel 2007 it no longer closes Excel, only the
> >> >> >> > active workbook. I have tried everything to no avail and have the same
> >> >> >> > results whether the workbook is in 2003 or 2007 format. Thanks in advance,
> >> >> >> > Carl
> >> >> >> >
> >> >> >> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >> >> >> > Dim oCtrl As Office.CommandBarControl
> >> >> >> > For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
> >> >> >> > oCtrl.Enabled = True
> >> >> >> > Next oCtrl
> >> >> >> > Application.DisplayFullScreen = False
> >> >> >> > Application.Quit
> >> >> >> > End Sub
> >> >>
> >>
>
|
Similar Threads
