In article ,
"Bernie Deitrick" <deitbe @ consumer dot org wrote:

Dennis,

If you want the same split on each sheet, then you could simply use the
workbook's worksheet change event: in the codemodule of the Thisworkbook
object, paste this code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Dim myR As Range
If ActiveWindow.FreezePanes Then Exit Sub
' Or use
' If ActiveWindow.Split Then Exit Sub
Application.EnableEvents = False
Set myR = Selection
Range("A1").Select
Range("C3").Select
ActiveWindow.FreezePanes = True
'ActiveWindow.Split = True
Application.Goto myR, Scroll:=True
Application.EnableEvents = True
End Sub

If you wanted different splits dependent of the worksheet, then you would
need to have some decision code, like:

Range("A1").Select
Range("C3").Select

If Sh.Name = "Sheet1" then
Range("A1").Select
Range("E4").Select
ElseIf......


HTH,
Bernie
MS Excel MVP


Bernie,

Thank you.

A question regarding customizing your code above...

I have 10 sheets with identical layouts, named 'system1, system2, etc.',
and 2 sheets each with other names and different freeze layouts. Would
you show me how to do the: [If Sh.Name = "Sheet1" then] statement for
this situation? ...This code is very new to me, but amazing and fun.

Is there a performance downside to using this code? Or, is it negligible?

Does this code break anything else that I should be aware of?

-Dennis

------------------------------------



"dk_" wrote in message
...
In article ,
"Bernie Deitrick" <deitbe @ consumer dot org wrote:

You could undo the removal: Copy the code below, right-click the
sheet tab, select "View Code", and paste the code into the window
that appears. It will re-freeze the window at cell C3.... It might
be a bit unsettling to your users (personally, I would remove code
like this), but it will work.


HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myR As Range
If ActiveWindow.FreezePanes Then Exit Sub
' Or use
' If ActiveWindow.Split Then Exit Sub
Application.EnableEvents = False
Set myR = Selection
Range("A1").Select
Range("C3").Select
ActiveWindow.FreezePanes = True
'ActiveWindow.Split = True
Application.Goto myR, Scroll:=True
Application.EnableEvents = True

End Sub

-----------------
Bernie,

That is cute! ;) Any more like that?

How would I use your code for a workbook with 12 sheets?

....can the code be fixed to be global for the workbook (all sheets), or
do I need to paste the code into each individual worksheet?

Besides surprising the user, is there any downside to implementing this
tactic?

Thanks again. That is an unsettling event for the user, but most
effective! :)

-Dennis

---------------------------------



"dk_" wrote in message
...
I asked this question a couple of days ago, but no one left a solution,
or suggestion that will work.

Can anyone post code that will disable: 'Remove Split' and 'Unfreeze
Panes', on individual sheets or every sheet in a workbook?

...I understand that if I check the Protect workbood for: 'Windows' box
in the Protect Workbook dialog box, this will disable 'Remove Split'
and
'Unfreeze Panes'.

However, when the above option is checked, it causes each sheet window
in the workbook to seperate from the main Excel Program window, into
the
'multiple document interface' (MDI) style of displaying windows. Then
each sheet window will be smallaer that the program main window, with
no
control buttons in the upper right to expand the window, and each sheet
will display a title bar. I don't want that. I would like to display
each sheet maxamized to the full program window, and have the user not
be permitted to Remove Split, or Unfreeze Panes.

Can this be done?

Thanks.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture