Split Window

Through some sort of an odd excel bug when updaing my pivot table
programmatically (Excel 2000) the window splits. The sheet that is being
updated is never actually selected and if I could I would like to avoid
having to select the sheet. Since split is part of the window object, not the
sheet object how do I remove the split without selecting the sheet? I have
looked at the object model and I am a little baffled as to how I can go about
this...
--
TIA...

Jim Thomlinson

Hi,

-If the window is the active one you could use:
With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
-or
knowing the sheet Sh and assuming the window is the first one of the
workbook (you can adapt the code otherwise):
with sh.parent.windows(1) 'window 1 of the book
.SplitColumn = 0
.SplitRow = 0
end with
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Jim Thomlinson" wrote:

> Through some sort of an odd excel bug when updaing my pivot table
> programmatically (Excel 2000) the window splits. The sheet that is being
> updated is never actually selected and if I could I would like to avoid
> having to select the sheet. Since split is part of the window object, not the
> sheet object how do I remove the split without selecting the sheet? I have
> looked at the object model and I am a little baffled as to how I can go about
> this...
> --
> TIA...
>
> Jim Thomlinson

That code only works if the sheet with the split screen is the active sheet.
At no point in my code do I select or activate the sheet with the split
screen. I know I can just select the sheet and run your code, but I was
wondering if it is possible to remove the split without activating the sheet?
--
HTH...

Jim Thomlinson


"sebastienm" wrote:

> Hi,
>
> -If the window is the active one you could use:
> With ActiveWindow
> .SplitColumn = 0
> .SplitRow = 0
> End With
> -or
> knowing the sheet Sh and assuming the window is the first one of the
> workbook (you can adapt the code otherwise):
> with sh.parent.windows(1) 'window 1 of the book
> .SplitColumn = 0
> .SplitRow = 0
> end with
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "Jim Thomlinson" wrote:
>
> > Through some sort of an odd excel bug when updaing my pivot table
> > programmatically (Excel 2000) the window splits. The sheet that is being
> > updated is never actually selected and if I could I would like to avoid
> > having to select the sheet. Since split is part of the window object, not the
> > sheet object how do I remove the split without selecting the sheet? I have
> > looked at the object model and I am a little baffled as to how I can go about
> > this...
> > --
> > TIA...
> >
> > Jim Thomlinson

I see what you meant now.
Sorry no idea. As you said earlier, the porperty comes from the Window
object and therefore affects only the active sheet.

Have you tried protecting the workbook window before refreshing the pivot
then resetting to normal after that?

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Jim Thomlinson" wrote:

> That code only works if the sheet with the split screen is the active sheet.
> At no point in my code do I select or activate the sheet with the split
> screen. I know I can just select the sheet and run your code, but I was
> wondering if it is possible to remove the split without activating the sheet?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "sebastienm" wrote:
>
> > Hi,
> >
> > -If the window is the active one you could use:
> > With ActiveWindow
> > .SplitColumn = 0
> > .SplitRow = 0
> > End With
> > -or
> > knowing the sheet Sh and assuming the window is the first one of the
> > workbook (you can adapt the code otherwise):
> > with sh.parent.windows(1) 'window 1 of the book
> > .SplitColumn = 0
> > .SplitRow = 0
> > end with
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Through some sort of an odd excel bug when updaing my pivot table
> > > programmatically (Excel 2000) the window splits. The sheet that is being
> > > updated is never actually selected and if I could I would like to avoid
> > > having to select the sheet. Since split is part of the window object, not the
> > > sheet object how do I remove the split without selecting the sheet? I have
> > > looked at the object model and I am a little baffled as to how I can go about
> > > this...
> > > --
> > > TIA...
> > >
> > > Jim Thomlinson

Hi Jim,

I've looked into similar before and I don't think there's a direct solution.
I assume the reason you don't want to select your sheet is even with
screenupdating disabled a flicker can occur.

However providing the problem split sheet is activated it does not need to
be the active sheet. In other words its workbook does not need to be active
and could even be hidden.

Try this with a split and activated sheet in non-active or hidden Book2

Sub test2()
Dim wns As Windows
Dim wn As Window

Set wns = Workbooks("Book2").Windows
On Error GoTo errH:
For Each wn In wns
If wn.Split Then
wn.FreezePanes = False
wn.Split = False
End If
resHere:
Next
On Error GoTo 0
Exit Sub
errH:
Resume resHere
'possible errors
' chart selected in activesheet (ie only if active wb)
' chart sheet activated
' protection
End Sub

I have also had the odd occasion of unintentionally splitting a window, not
with a pivot table but don't recall why.

Regards,
Peter T




"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> That code only works if the sheet with the split screen is the active
sheet.
> At no point in my code do I select or activate the sheet with the split
> screen. I know I can just select the sheet and run your code, but I was
> wondering if it is possible to remove the split without activating the
sheet?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "sebastienm" wrote:
>
> > Hi,
> >
> > -If the window is the active one you could use:
> > With ActiveWindow
> > .SplitColumn = 0
> > .SplitRow = 0
> > End With
> > -or
> > knowing the sheet Sh and assuming the window is the first one of the
> > workbook (you can adapt the code otherwise):
> > with sh.parent.windows(1) 'window 1 of the book
> > .SplitColumn = 0
> > .SplitRow = 0
> > end with
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Through some sort of an odd excel bug when updaing my pivot table
> > > programmatically (Excel 2000) the window splits. The sheet that is
being
> > > updated is never actually selected and if I could I would like to
avoid
> > > having to select the sheet. Since split is part of the window object,
not the
> > > sheet object how do I remove the split without selecting the sheet? I
have
> > > looked at the object model and I am a little baffled as to how I can
go about
> > > this...
> > > --
> > > TIA...
> > >
> > > Jim Thomlinson

Hi,

I also don't know about handling the split without selecting the sheet.
but how about creating a new window and closing the old, or using a
custome view:

ActiveWorkbook.CustomViews.Add "tmp", False, False

'your code

ActiveWorkbook.CustomViews("tmp").Show
ActiveWorkbook.CustomViews("tmp").Delete

--
HTH,

okaizawa


Jim Thomlinson wrote:
> That code only works if the sheet with the split screen is the active sheet.
> At no point in my code do I select or activate the sheet with the split
> screen. I know I can just select the sheet and run your code, but I was
> wondering if it is possible to remove the split without activating the sheet?

Looks like this is one of those occasions where you actually have to select
the sheet. The only reason I don't want to select is that IMO selects should
be avoided wherever possible. They are slow and combersome and in 99% of
cases can be avoided with good code...
--
HTH...

Jim Thomlinson


"sebastienm" wrote:

> I see what you meant now.
> Sorry no idea. As you said earlier, the porperty comes from the Window
> object and therefore affects only the active sheet.
>
> Have you tried protecting the workbook window before refreshing the pivot
> then resetting to normal after that?
>
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "Jim Thomlinson" wrote:
>
> > That code only works if the sheet with the split screen is the active sheet.
> > At no point in my code do I select or activate the sheet with the split
> > screen. I know I can just select the sheet and run your code, but I was
> > wondering if it is possible to remove the split without activating the sheet?
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "sebastienm" wrote:
> >
> > > Hi,
> > >
> > > -If the window is the active one you could use:
> > > With ActiveWindow
> > > .SplitColumn = 0
> > > .SplitRow = 0
> > > End With
> > > -or
> > > knowing the sheet Sh and assuming the window is the first one of the
> > > workbook (you can adapt the code otherwise):
> > > with sh.parent.windows(1) 'window 1 of the book
> > > .SplitColumn = 0
> > > .SplitRow = 0
> > > end with
> > > --
> > > Regards,
> > > Sébastien
> > > <http://www.ondemandanalysis.com>
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Through some sort of an odd excel bug when updaing my pivot table
> > > > programmatically (Excel 2000) the window splits. The sheet that is being
> > > > updated is never actually selected and if I could I would like to avoid
> > > > having to select the sheet. Since split is part of the window object, not the
> > > > sheet object how do I remove the split without selecting the sheet? I have
> > > > looked at the object model and I am a little baffled as to how I can go about
> > > > this...
> > > > --
> > > > TIA...
> > > >
> > > > Jim Thomlinson

I agree. I avoid selecting whenever i can too.

I checked the Xl4macro functions to see if there were some feature to work
on the split, but nothing more than the current functionality.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Jim Thomlinson" wrote:

> Looks like this is one of those occasions where you actually have to select
> the sheet. The only reason I don't want to select is that IMO selects should
> be avoided wherever possible. They are slow and combersome and in 99% of
> cases can be avoided with good code...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "sebastienm" wrote:
>
> > I see what you meant now.
> > Sorry no idea. As you said earlier, the porperty comes from the Window
> > object and therefore affects only the active sheet.
> >
> > Have you tried protecting the workbook window before refreshing the pivot
> > then resetting to normal after that?
> >
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > That code only works if the sheet with the split screen is the active sheet.
> > > At no point in my code do I select or activate the sheet with the split
> > > screen. I know I can just select the sheet and run your code, but I was
> > > wondering if it is possible to remove the split without activating the sheet?
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "sebastienm" wrote:
> > >
> > > > Hi,
> > > >
> > > > -If the window is the active one you could use:
> > > > With ActiveWindow
> > > > .SplitColumn = 0
> > > > .SplitRow = 0
> > > > End With
> > > > -or
> > > > knowing the sheet Sh and assuming the window is the first one of the
> > > > workbook (you can adapt the code otherwise):
> > > > with sh.parent.windows(1) 'window 1 of the book
> > > > .SplitColumn = 0
> > > > .SplitRow = 0
> > > > end with
> > > > --
> > > > Regards,
> > > > Sébastien
> > > > <http://www.ondemandanalysis.com>
> > > >
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > Through some sort of an odd excel bug when updaing my pivot table
> > > > > programmatically (Excel 2000) the window splits. The sheet that is being
> > > > > updated is never actually selected and if I could I would like to avoid
> > > > > having to select the sheet. Since split is part of the window object, not the
> > > > > sheet object how do I remove the split without selecting the sheet? I have
> > > > > looked at the object model and I am a little baffled as to how I can go about
> > > > > this...
> > > > > --
> > > > > TIA...
> > > > >
> > > > > Jim Thomlinson