Code to prevent Remove Split or Unfreeze Panes?

[dk_]

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

 

[Bernie Deitrick]

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

 

[dk_]

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

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

 

[Bernie Deitrick]

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

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_]

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

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

 

[Bernie Deitrick]

Dennis,

Something like this should work:

If Sh.Name Like "System*" then
Range("A1").Select
Range("E4").Select
ElseIf Sh.Name = "Other Name" then
Range("A1").Select
Range("H5").Select
ElseIf Sh.Name = "Last Name" then
Range("A1").Select
Range("G9").Select
End if

ActiveWindow.FreezePanes = True
Application.Goto myR, Scroll:=True

There really shouldn't be a noticeable performance cost, since the user will
be selecting cells, not doing heavy calculations, when this code is run.

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_]

Bernie,

Thank you once again.

I have been playing with the code and it definately works, but I can't
figure out exactly what each bit of code is doing.

When I change one or both of the ranges, or even if I don't change the
range, apparently depending on which cell is selected, I get a different
display redraws. I mean that the page will scroll to different locations.

Would your mind explaining each line of code?

For example, Range().select is repeated, what is the difference between
the first Range and the second? What if I leave one out? What do the
other lines of code do?

I'd like to learn by taking your code apart and playing with it.

Thanks.

-Dennis


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

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

Dennis,

Something like this should work:

If Sh.Name Like "System*" then
Range("A1").Select
Range("E4").Select
ElseIf Sh.Name = "Other Name" then
Range("A1").Select
Range("H5").Select
ElseIf Sh.Name = "Last Name" then
Range("A1").Select
Range("G9").Select
End if

ActiveWindow.FreezePanes = True
Application.Goto myR, Scroll:=True

There really shouldn't be a noticeable performance cost, since the user will
be selecting cells, not doing heavy calculations, when this code is run.

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

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

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

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



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