Protect sheet while enabling outlines and custom views

[hojikuru]

I saw several posts outlining how to enable the outline function (Data
.... Group and Outline ... Group). Very helpful. Thanks.

Is there some way also to enable custom views which hide certain
*sheets* and hide *rows/columns*?

I have the following code that runs when the workbook is opened:
--------------------------------
Private Sub Workbook_Open()
ActiveWorkbook.CustomViews("Navigation").Show
MsgBox "Please select the appropriate view on the Navigation tab:
summaries on left, working views on right.", vbOKOnly, "[snip name]"
End Sub
--------------------------------
So, the user interface depends on the use of radio buttons, which in
turn call out custom views as such (for example):
--------------------------------
Private Sub OptionButton7_Click()
ActiveWorkbook.CustomViews("2004 Q1 Full View").Show
End Sub
--------------------------------

So, if I want both the outline function to work and the custom views
to work in a *protected and shared* worksheet, is there any way to do
that?

Will the ranges I have defined for users to be able to edit still work
if protection is invoked from VBA upon workbook open, or will I have
to explicitly define editable ranges in the VBA code?

Thanks, ... and pardon the long list of questions.
Michael Lambert.
(e-mail address removed)

 

[Dave Peterson]

If you protect the sheet in code, you can enableoutlining.

But the bad news is you can't change the protection of a worksheet in a shared
workbook.

I think you have to make a choice.

I saw several posts outlining how to enable the outline function (Data
... Group and Outline ... Group). Very helpful. Thanks.

Is there some way also to enable custom views which hide certain
*sheets* and hide *rows/columns*?

I have the following code that runs when the workbook is opened:
--------------------------------
Private Sub Workbook_Open()
ActiveWorkbook.CustomViews("Navigation").Show
MsgBox "Please select the appropriate view on the Navigation tab:
summaries on left, working views on right.", vbOKOnly, "[snip name]"
End Sub
--------------------------------
So, the user interface depends on the use of radio buttons, which in
turn call out custom views as such (for example):
--------------------------------
Private Sub OptionButton7_Click()
ActiveWorkbook.CustomViews("2004 Q1 Full View").Show
End Sub
--------------------------------

So, if I want both the outline function to work and the custom views
to work in a *protected and shared* worksheet, is there any way to do
that?

Will the ranges I have defined for users to be able to edit still work
if protection is invoked from VBA upon workbook open, or will I have
to explicitly define editable ranges in the VBA code?

Thanks, ... and pardon the long list of questions.
Michael Lambert.
(e-mail address removed)

 

[hojikuru]

Thanks for replying, Dave.

Can I enable outlining (and hide/unhide columns/rows), protect the
workbook, then share it? Will the outlining still work once the
workbook is closed and reopened?

I don't mind leaving the workbook protected once it's shared, but I
need to have both some way, while still being able to use outline and
custom views.

Thanks,
ML.

If you protect the sheet in code, you can enableoutlining.

But the bad news is you can't change the protection of a worksheet in a shared
workbook.

I think you have to make a choice.

I saw several posts outlining how to enable the outline function (Data
... Group and Outline ... Group). Very helpful. Thanks.

Is there some way also to enable custom views which hide certain
*sheets* and hide *rows/columns*?

I have the following code that runs when the workbook is opened:
--------------------------------
Private Sub Workbook_Open()
ActiveWorkbook.CustomViews("Navigation").Show
MsgBox "Please select the appropriate view on the Navigation tab:
summaries on left, working views on right.", vbOKOnly, "[snip name]"
End Sub
--------------------------------
So, the user interface depends on the use of radio buttons, which in
turn call out custom views as such (for example):
--------------------------------
Private Sub OptionButton7_Click()
ActiveWorkbook.CustomViews("2004 Q1 Full View").Show
End Sub
--------------------------------

So, if I want both the outline function to work and the custom views
to work in a *protected and shared* worksheet, is there any way to do
that?

Will the ranges I have defined for users to be able to edit still work
if protection is invoked from VBA upon workbook open, or will I have
to explicitly define editable ranges in the VBA code?

Thanks, ... and pardon the long list of questions.
Michael Lambert.
(e-mail address removed)

 

[Dave Peterson]

The .enableoutlining has to be set each time the workbook opens. So you've
still got the problem.

Debra Dalgleish suggested a possible workaround for autofiltering.

She said something like create a new worksheet (unprotected).
fill it with formulas like:
=if(sheet1!a1="","",sheet1!a1)

And put your outlining there.


In xl2002, they have an option to allow autofiltering on protected worksheets.
Maybe outlining will see its day in the sun in a future version.

Thanks for replying, Dave.

Can I enable outlining (and hide/unhide columns/rows), protect the
workbook, then share it? Will the outlining still work once the
workbook is closed and reopened?

I don't mind leaving the workbook protected once it's shared, but I
need to have both some way, while still being able to use outline and
custom views.

Thanks,
ML.

If you protect the sheet in code, you can enableoutlining.

But the bad news is you can't change the protection of a worksheet in a shared
workbook.

I think you have to make a choice.

I saw several posts outlining how to enable the outline function (Data
... Group and Outline ... Group). Very helpful. Thanks.

Is there some way also to enable custom views which hide certain
*sheets* and hide *rows/columns*?

I have the following code that runs when the workbook is opened:
--------------------------------
Private Sub Workbook_Open()
ActiveWorkbook.CustomViews("Navigation").Show
MsgBox "Please select the appropriate view on the Navigation tab:
summaries on left, working views on right.", vbOKOnly, "[snip name]"
End Sub
--------------------------------
So, the user interface depends on the use of radio buttons, which in
turn call out custom views as such (for example):
--------------------------------
Private Sub OptionButton7_Click()
ActiveWorkbook.CustomViews("2004 Q1 Full View").Show
End Sub
--------------------------------

So, if I want both the outline function to work and the custom views
to work in a *protected and shared* worksheet, is there any way to do
that?

Will the ranges I have defined for users to be able to edit still work
if protection is invoked from VBA upon workbook open, or will I have
to explicitly define editable ranges in the VBA code?

Thanks, ... and pardon the long list of questions.
Michael Lambert.
(e-mail address removed)

 

[hojikuru]

(I'm using XL 2002.)

Actually, Dave, this sounds somewhat promising. Would this work ...

1. Protect the workbook.
2. Share it. (assumption is that protection stays active from this
point.)
3. enableoutlining in the workbookopen module, so it sets the
parameter each time the workbook is opened.

Neither protection nor sharing would change, but the nonpersistent
..enableoutlining parameter could be reset upon each open.

If this would work, would you be so kind as to take a stab at the code
I would use (one-liner, I am guessing) and confirm that I put it in
the ThisWorkbook object (under Workbook / Open)? I'm very new to VBA
and could use all help avail.

Thanks.
ML

The .enableoutlining has to be set each time the workbook opens. So you've
still got the problem.

Debra Dalgleish suggested a possible workaround for autofiltering.

She said something like create a new worksheet (unprotected).
fill it with formulas like:
=if(sheet1!a1="","",sheet1!a1)

And put your outlining there.


In xl2002, they have an option to allow autofiltering on protected worksheets.
Maybe outlining will see its day in the sun in a future version.

Thanks for replying, Dave.

Can I enable outlining (and hide/unhide columns/rows), protect the
workbook, then share it? Will the outlining still work once the
workbook is closed and reopened?

I don't mind leaving the workbook protected once it's shared, but I
need to have both some way, while still being able to use outline and
custom views.

Thanks,
ML.

If you protect the sheet in code, you can enableoutlining.

But the bad news is you can't change the protection of a worksheet in a shared
workbook.

I think you have to make a choice.


(e-mail address removed) wrote:

I saw several posts outlining how to enable the outline function (Data
... Group and Outline ... Group). Very helpful. Thanks.

Is there some way also to enable custom views which hide certain
*sheets* and hide *rows/columns*?

I have the following code that runs when the workbook is opened:
--------------------------------
Private Sub Workbook_Open()
ActiveWorkbook.CustomViews("Navigation").Show
MsgBox "Please select the appropriate view on the Navigation tab:
summaries on left, working views on right.", vbOKOnly, "[snip name]"
End Sub
--------------------------------
So, the user interface depends on the use of radio buttons, which in
turn call out custom views as such (for example):
--------------------------------
Private Sub OptionButton7_Click()
ActiveWorkbook.CustomViews("2004 Q1 Full View").Show
End Sub
--------------------------------

So, if I want both the outline function to work and the custom views
to work in a *protected and shared* worksheet, is there any way to do
that?

Will the ranges I have defined for users to be able to edit still work
if protection is invoked from VBA upon workbook open, or will I have
to explicitly define editable ranges in the VBA code?

Thanks, ... and pardon the long list of questions.
Michael Lambert.
(e-mail address removed)

 

[Dave Peterson]

Your code will look something like:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub


But since you don't want to change protection, you can comment that line out by
putting an apostrophe in front of it:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
' .Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Try it and see what happens--but don't get your hopes up.

(I'm using XL 2002.)

Actually, Dave, this sounds somewhat promising. Would this work ...

1. Protect the workbook.
2. Share it. (assumption is that protection stays active from this
point.)
3. enableoutlining in the workbookopen module, so it sets the
parameter each time the workbook is opened.

Neither protection nor sharing would change, but the nonpersistent
.enableoutlining parameter could be reset upon each open.

If this would work, would you be so kind as to take a stab at the code
I would use (one-liner, I am guessing) and confirm that I put it in
the ThisWorkbook object (under Workbook / Open)? I'm very new to VBA
and could use all help avail.

Thanks.
ML

The .enableoutlining has to be set each time the workbook opens. So you've
still got the problem.

Debra Dalgleish suggested a possible workaround for autofiltering.

She said something like create a new worksheet (unprotected).
fill it with formulas like:
=if(sheet1!a1="","",sheet1!a1)

And put your outlining there.


In xl2002, they have an option to allow autofiltering on protected worksheets.
Maybe outlining will see its day in the sun in a future version.

Thanks for replying, Dave.

Can I enable outlining (and hide/unhide columns/rows), protect the
workbook, then share it? Will the outlining still work once the
workbook is closed and reopened?

I don't mind leaving the workbook protected once it's shared, but I
need to have both some way, while still being able to use outline and
custom views.

Thanks,
ML.

If you protect the sheet in code, you can enableoutlining.

But the bad news is you can't change the protection of a worksheet in a shared
workbook.

I think you have to make a choice.


(e-mail address removed) wrote:

I saw several posts outlining how to enable the outline function (Data
... Group and Outline ... Group). Very helpful. Thanks.

Is there some way also to enable custom views which hide certain
*sheets* and hide *rows/columns*?

I have the following code that runs when the workbook is opened:
--------------------------------
Private Sub Workbook_Open()
ActiveWorkbook.CustomViews("Navigation").Show
MsgBox "Please select the appropriate view on the Navigation tab:
summaries on left, working views on right.", vbOKOnly, "[snip name]"
End Sub
--------------------------------
So, the user interface depends on the use of radio buttons, which in
turn call out custom views as such (for example):
--------------------------------
Private Sub OptionButton7_Click()
ActiveWorkbook.CustomViews("2004 Q1 Full View").Show
End Sub
--------------------------------

So, if I want both the outline function to work and the custom views
to work in a *protected and shared* worksheet, is there any way to do
that?

Will the ranges I have defined for users to be able to edit still work
if protection is invoked from VBA upon workbook open, or will I have
to explicitly define editable ranges in the VBA code?

Thanks, ... and pardon the long list of questions.
Michael Lambert.
(e-mail address removed)

 

[hojikuru]

This approach seems unstable, particularly since my workflow depends
heavily on custom views, which don't seem to do well with protection.
I think my best bet will be to go with an unprotected workbook, but
detect when a user tries to select a member of a Named Range. I would
send him to cell A1. In this case, here's what I'm trying to do, but
it doesn't work ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("AGRegionsProtected").Address Then
ActiveCell = Range("A1").Select
End Sub

As a secondary concern, I'd also like to have a pop-up explain what is
going on. I could use this code:
MsgBox "This area is automatically calculated and should not be
altered.", vbCritical, Error

As a completely different approach, setting the scroll area to exclude
the range I want to protect (in this case, AGRegionsProtected), may
work.

This is difficult since the workbook must be shared, I have outlines
and custom views ... all in one.

TIA,
ML.

 

[Dave Peterson]

That may work.

You could stop them from selecting or you could let them select, but stop them
from changing. (But both these techniques fail if the user disables macros or
turns off events.)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
Application.EnableEvents = False
Me.Range("a1").Select
Application.EnableEvents = True
MsgBox "Please don't get near that radio active range!"
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

On Error GoTo errHandler:

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this range!"
End If

errHandler:
Application.EnableEvents = True

End Sub

This approach seems unstable, particularly since my workflow depends
heavily on custom views, which don't seem to do well with protection.
I think my best bet will be to go with an unprotected workbook, but
detect when a user tries to select a member of a Named Range. I would
send him to cell A1. In this case, here's what I'm trying to do, but
it doesn't work ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("AGRegionsProtected").Address Then
ActiveCell = Range("A1").Select
End Sub

As a secondary concern, I'd also like to have a pop-up explain what is
going on. I could use this code:
MsgBox "This area is automatically calculated and should not be
altered.", vbCritical, Error

As a completely different approach, setting the scroll area to exclude
the range I want to protect (in this case, AGRegionsProtected), may
work.

This is difficult since the workbook must be shared, I have outlines
and custom views ... all in one.

TIA,
ML.

Your code will look something like:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub


But since you don't want to change protection, you can comment that line out by
putting an apostrophe in front of it:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
' .Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Try it and see what happens--but don't get your hopes up.
[snip]

 

[hojikuru]

Boy, this is great! Thanks. I like your idea -- the second approach
-- better. Not only will it be good to let the users select the
protected range so they can copy it, but the .undo touch makes me feel
much more comfortable about not being able to protect this workbook.

The only problem with how it functions now is that after .undo
reverses the edit, if the user selects another cell in the protected
range, Excel re-does the edit (maybe undoing the undo?), and it can
get caught in a loop. Alternately, is the .undo action *itself*
considered a selectionchange, so might it be triggering itself to
fire?

If I save the workbook, the loop stops. Until it is saved, it keeps
reiterating the undo/redo loop whenever another cell in the protected
range is selected.

Is there some way to clear the undo buffer right after it executes?

That may work.

You could stop them from selecting or you could let them select, but stop them
from changing. (But both these techniques fail if the user disables macros or
turns off events.)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
Application.EnableEvents = False
Me.Range("a1").Select
Application.EnableEvents = True
MsgBox "Please don't get near that radio active range!"
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

On Error GoTo errHandler:

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this range!"
End If

errHandler:
Application.EnableEvents = True

End Sub

[snip]

 

[Dave Peterson]

Mmmmmm.

First, oops. I got the code for each routine in the wrong one.

The code for the worksheet_Change was under _selectionchange (and vice versa).
(well, it looks that way today! But maybe someone changed it after I posted. I
blame the gremlins.)

It should have read:

Option Explicit
Private Sub Worksheet_selectionChange(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
Application.EnableEvents = False
Me.Range("a1").Select
Application.EnableEvents = True
MsgBox "Please don't get near that radio active range!"
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

On Error GoTo errHandler:

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this range!"
End If

errHandler:
Application.EnableEvents = True

End Sub

But I think I would only use one of them (the current! worksheet_change if I had
my choice.)

Sorry about my confusion.

===
When I was testing, the .undo undid my range naming (the last thing I did). It
was sure irritating.

Boy, this is great! Thanks. I like your idea -- the second approach
-- better. Not only will it be good to let the users select the
protected range so they can copy it, but the .undo touch makes me feel
much more comfortable about not being able to protect this workbook.

The only problem with how it functions now is that after .undo
reverses the edit, if the user selects another cell in the protected
range, Excel re-does the edit (maybe undoing the undo?), and it can
get caught in a loop. Alternately, is the .undo action *itself*
considered a selectionchange, so might it be triggering itself to
fire?

If I save the workbook, the loop stops. Until it is saved, it keeps
reiterating the undo/redo loop whenever another cell in the protected
range is selected.

Is there some way to clear the undo buffer right after it executes?

That may work.

You could stop them from selecting or you could let them select, but stop them
from changing. (But both these techniques fail if the user disables macros or
turns off events.)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
Application.EnableEvents = False
Me.Range("a1").Select
Application.EnableEvents = True
MsgBox "Please don't get near that radio active range!"
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

On Error GoTo errHandler:

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this range!"
End If

errHandler:
Application.EnableEvents = True

End Sub

[snip]

 

[hojikuru]

Tight as Fort Knox! Thanks for the help!

One problem I ran into (it's fixed) is that the total number of cells
I had to include was too long for the Insert ... Name ... Define
dialog, so I had to define two different ranges and put a loop in the
code as such:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected1")

On Error GoTo errHandler:

If Intersect(Target, myRng) Is Nothing Then
' Check whether they're trying to edit AGRegionsProtected2
Set myRng = Me.Range("AGRegionsProtected2")
If Intersect(Target, myRng) Is Nothing Then
' let 'em do it. target is in neither 1 nor 2.
Else
' Undoing edit on AGRegionsProtected2 range
With Application
.EnableEvents = False
.Undo
End With
MsgBox "This is a protected range filled with formulas
only. Editing not allowed.", vbCritical, "ERROR! DO NOT EDIT!"
End If
Else
' Undoing edit on AGRegionsProtected1 range
With Application
.EnableEvents = False
.Undo
End With
MsgBox "This is a protected range filled with formulas only.
Editing not allowed.", vbCritical, "ERROR! DO NOT EDIT!"
End If
errHandler:
Application.EnableEvents = True

End Sub

This was my first attempt at "real" code. Thanks for the help, Dave.

The only other thing I'm going to try to figure out is how to disable
editing/deleting (tampering with) my defined name ranges. There's got
to be a way to disable that on workbookopen; I'm open to tips. Other
than that -- assuming the user enables macros -- I think I'm pretty
covered.

Again, I can't say how appreciative I am.

Regards,
Michael

P.S. Is there a good place I can reference for definitions? For
example, I see lots of folks start their code with Dim, but I don't
know what it is and can't seem to find anywhere that defines it.

Mmmmmm.

First, oops. I got the code for each routine in the wrong one.

The code for the worksheet_Change was under _selectionchange (and vice versa).
(well, it looks that way today! But maybe someone changed it after I posted. I
blame the gremlins.)

It should have read:

Option Explicit
Private Sub Worksheet_selectionChange(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
Application.EnableEvents = False
Me.Range("a1").Select
Application.EnableEvents = True
MsgBox "Please don't get near that radio active range!"
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

On Error GoTo errHandler:

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this range!"
End If

errHandler:
Application.EnableEvents = True

End Sub

But I think I would only use one of them (the current! worksheet_change if I had
my choice.)

Sorry about my confusion.

===
When I was testing, the .undo undid my range naming (the last thing I did). It
was sure irritating.

[snip]

 

[Dave Peterson]

You could combine ranges:

Dim myRng1 As Range
Dim myRng2 As Range

Set myRng1 = Me.Range("AGRegionsProtected1")
Set myRng2 = Me.Range("AGRegionsProtected2")

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Union(myRng1, myRng2)) Is Nothing Then Exit Sub


==
You could even define a third range manually that's the combination of both of
these ranges:

Insert|range|define
Names in workbook:
AGRegionsProtectedAll

Refers to:
=AGRegionsProtected1,AGRegionsProtected2

You won't see it in the namebox dropdown, but you can type it in and select the
combined range.

Tight as Fort Knox! Thanks for the help!

One problem I ran into (it's fixed) is that the total number of cells
I had to include was too long for the Insert ... Name ... Define
dialog, so I had to define two different ranges and put a loop in the
code as such:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected1")

On Error GoTo errHandler:

If Intersect(Target, myRng) Is Nothing Then
' Check whether they're trying to edit AGRegionsProtected2
Set myRng = Me.Range("AGRegionsProtected2")
If Intersect(Target, myRng) Is Nothing Then
' let 'em do it. target is in neither 1 nor 2.
Else
' Undoing edit on AGRegionsProtected2 range
With Application
.EnableEvents = False
.Undo
End With
MsgBox "This is a protected range filled with formulas
only. Editing not allowed.", vbCritical, "ERROR! DO NOT EDIT!"
End If
Else
' Undoing edit on AGRegionsProtected1 range
With Application
.EnableEvents = False
.Undo
End With
MsgBox "This is a protected range filled with formulas only.
Editing not allowed.", vbCritical, "ERROR! DO NOT EDIT!"
End If
errHandler:
Application.EnableEvents = True

End Sub

This was my first attempt at "real" code. Thanks for the help, Dave.

The only other thing I'm going to try to figure out is how to disable
editing/deleting (tampering with) my defined name ranges. There's got
to be a way to disable that on workbookopen; I'm open to tips. Other
than that -- assuming the user enables macros -- I think I'm pretty
covered.

Again, I can't say how appreciative I am.

Regards,
Michael

P.S. Is there a good place I can reference for definitions? For
example, I see lots of folks start their code with Dim, but I don't
know what it is and can't seem to find anywhere that defines it.

Mmmmmm.

First, oops. I got the code for each routine in the wrong one.

The code for the worksheet_Change was under _selectionchange (and vice versa).
(well, it looks that way today! But maybe someone changed it after I posted. I
blame the gremlins.)

It should have read:

Option Explicit
Private Sub Worksheet_selectionChange(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
Application.EnableEvents = False
Me.Range("a1").Select
Application.EnableEvents = True
MsgBox "Please don't get near that radio active range!"
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")

On Error GoTo errHandler:

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this range!"
End If

errHandler:
Application.EnableEvents = True

End Sub

But I think I would only use one of them (the current! worksheet_change if I had
my choice.)

Sorry about my confusion.

===
When I was testing, the .undo undid my range naming (the last thing I did). It
was sure irritating.

[snip]

 

[hojikuru]

Again, thanks much, Dave. I think that the bottom of my previous post
got clipped. I had a couple questions:

The only other thing I'm going to try to figure out is how to disable
editing/deleting (tampering with) my defined name ranges. There's got
to be a way to disable that on workbookopen; I'm open to tips.

P.S. Is there a good place I can reference for definitions? For
example, I see lots of folks start their code with Dim, but I don't
know what it is and can't seem to find anywhere that defines it.

R/
Michael.

 

[Dave Peterson]

You could hide the names--it can be done in code, but even better, get a copy
of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) utility
"Name Manager.xla" from http://www.bmsltd.co.uk/mvp/
(or http://www.bmsltd.ie/mvp/)

There's a button that allows you to hide the name. (It makes it more difficult
for the average user to screw up your design.)

Debra Dalgleish has a page for books:
http://www.contextures.com/xlbooks.html
(John Walkenbach's book is very nice)

From a previous post about Dimming and "option explicit" which go hand in glove.

And if you use "option explicit" and dim your variables appropriately, then you
can get the VBE's intellisense to pop up for you. (Less wear and tear on the
memory!)

Try this in a test module:

Option explicit
sub testme()
Dim Wks as worksheet
'now type wks. (as soon as you hit that dot, you should see some valid choices
for what you can do to a worksheet (or a property of the worksheet that you can
examine).

This is very nice to us older folks.

And by forcing you to declare your variables, you don't have to spend hours
looking for the mistake in this code:

for xlctr = 1 to 5
msgbox x1ctr
next xlctr

(the one's and ell's are mixed up).

You would have gotten an error when you tried to compile.

And this is nice to us older folks with bifocals!

Again, thanks much, Dave. I think that the bottom of my previous post
got clipped. I had a couple questions:

The only other thing I'm going to try to figure out is how to disable
editing/deleting (tampering with) my defined name ranges. There's got
to be a way to disable that on workbookopen; I'm open to tips.

P.S. Is there a good place I can reference for definitions? For
example, I see lots of folks start their code with Dim, but I don't
know what it is and can't seem to find anywhere that defines it.

R/
Michael.

You could combine ranges:

Dim myRng1 As Range
Dim myRng2 As Range

Set myRng1 = Me.Range("AGRegionsProtected1")
Set myRng2 = Me.Range("AGRegionsProtected2")

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Union(myRng1, myRng2)) Is Nothing Then Exit Sub


==
You could even define a third range manually that's the combination of both of
these ranges:

Insert|range|define
Names in workbook:
AGRegionsProtectedAll

Refers to:
=AGRegionsProtected1,AGRegionsProtected2

You won't see it in the namebox dropdown, but you can type it in and select the
combined range.



[snip]