Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split Window
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split Window
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split Window
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split Window
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 resHe 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split Window
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split Window
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split Window
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove split a split window? | New Users to Excel | |||
How do I synchronize scroll split window tiles? | Excel Worksheet Functions | |||
split window | New Users to Excel | |||
double window split? | Excel Discussion (Misc queries) | |||
Killing the split window | Setting up and Configuration of Excel |