Using Custom Views in a protected worksheet
 

I want to use custom views for entering data and then printing it...

Not being the one doing the actual entering of the data, I don't want people
to inadvertantly delete colums or other info, so I need to protect the
worksheet.

My different views consist of some colums being hidden for data entry, and
then revealed for printing... but I haven't been able to make this work with
the worksheet being protected... no matter what options I check or uncheck
while protecting the sheet.

Any ideas..?

Thanks!
Pierre, Montreal

Using Custom Views in a protected worksheet
 

Here's one idea: if you have a Data Validation list that contains your Custom
View names and unprotect the cell it's in, you can use a change event. In
this case the list cell is F1:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range
Set rng = Range("J1")
' Only look at single cell changes
If Target.Count 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
ActiveWorkbook.CustomViews(Target.Value).Show
End Sub

HTH,

Smitty

"Pierre" wrote:

I want to use custom views for entering data and then printing it...

Not being the one doing the actual entering of the data, I don't want people
to inadvertantly delete colums or other info, so I need to protect the
worksheet.

My different views consist of some colums being hidden for data entry, and
then revealed for printing... but I haven't been able to make this work with
the worksheet being protected... no matter what options I check or uncheck
while protecting the sheet.

Any ideas..?

Thanks!
Pierre, Montreal

Using Custom Views in a protected worksheet
 

Smitty,

Thanks so much for the reply... except it would only be great if the problem
was that once protected we did not have access to the actual FUNCTION of
changing the views... but the problem lies in that the fact that since the
sheet IS protected, it will not allow for colums to be unhidded or hidden.

So whether I change the views traditionally or with your example, the same
problem remains... unfortunately.

If you have any other thoughts, I'll try anything.

PS: I was initially using "group" and "ungroup" but protected sheets won't
allow for ungrouping either...

:-(
--
Pierre
Montreal


"Smitty" wrote:

Here's one idea: if you have a Data Validation list that contains your Custom
View names and unprotect the cell it's in, you can use a change event. In
this case the list cell is F1:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range
Set rng = Range("J1")
' Only look at single cell changes
If Target.Count 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
ActiveWorkbook.CustomViews(Target.Value).Show
End Sub

HTH,

Smitty

"Pierre" wrote:

I want to use custom views for entering data and then printing it...

Not being the one doing the actual entering of the data, I don't want people
to inadvertantly delete colums or other info, so I need to protect the
worksheet.

My different views consist of some colums being hidden for data entry, and
then revealed for printing... but I haven't been able to make this work with
the worksheet being protected... no matter what options I check or uncheck
while protecting the sheet.

Any ideas..?

Thanks!
Pierre, Montreal

Using Custom Views in a protected worksheet
 

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

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

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Pierre wrote:

Smitty,

Thanks so much for the reply... except it would only be great if the problem
was that once protected we did not have access to the actual FUNCTION of
changing the views... but the problem lies in that the fact that since the
sheet IS protected, it will not allow for colums to be unhidded or hidden.

So whether I change the views traditionally or with your example, the same
problem remains... unfortunately.

If you have any other thoughts, I'll try anything.

PS: I was initially using "group" and "ungroup" but protected sheets won't
allow for ungrouping either...

:-(
--
Pierre
Montreal

"Smitty" wrote:

Here's one idea: if you have a Data Validation list that contains your Custom
View names and unprotect the cell it's in, you can use a change event. In
this case the list cell is F1:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range
Set rng = Range("J1")
' Only look at single cell changes
If Target.Count 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
ActiveWorkbook.CustomViews(Target.Value).Show
End Sub

HTH,

Smitty

"Pierre" wrote:

I want to use custom views for entering data and then printing it...

Not being the one doing the actual entering of the data, I don't want people
to inadvertantly delete colums or other info, so I need to protect the
worksheet.

My different views consist of some colums being hidden for data entry, and
then revealed for printing... but I haven't been able to make this work with
the worksheet being protected... no matter what options I check or uncheck
while protecting the sheet.

Any ideas..?

Thanks!
Pierre, Montreal

--

Dave Peterson

Using Custom Views in a protected worksheet
 

You can address the protection like this:

ActiveSheet.Unprotect "password"
ActiveWorkbook.CustomViews(Target.Value).Show
ActiveSheet.Protect "password"

Sorry about neglecting that part previously.

Smitty


"Dave Peterson" wrote:

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

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

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Pierre wrote:

Smitty,

Thanks so much for the reply... except it would only be great if the problem
was that once protected we did not have access to the actual FUNCTION of
changing the views... but the problem lies in that the fact that since the
sheet IS protected, it will not allow for colums to be unhidded or hidden.

So whether I change the views traditionally or with your example, the same
problem remains... unfortunately.

If you have any other thoughts, I'll try anything.

PS: I was initially using "group" and "ungroup" but protected sheets won't
allow for ungrouping either...

:-(
--
Pierre
Montreal

"Smitty" wrote:

Here's one idea: if you have a Data Validation list that contains your Custom
View names and unprotect the cell it's in, you can use a change event. In
this case the list cell is F1:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range
Set rng = Range("J1")
' Only look at single cell changes
If Target.Count 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
ActiveWorkbook.CustomViews(Target.Value).Show
End Sub

HTH,

Smitty

"Pierre" wrote:

I want to use custom views for entering data and then printing it...

Not being the one doing the actual entering of the data, I don't want people
to inadvertantly delete colums or other info, so I need to protect the
worksheet.

My different views consist of some colums being hidden for data entry, and
then revealed for printing... but I haven't been able to make this work with
the worksheet being protected... no matter what options I check or uncheck
while protecting the sheet.

Any ideas..?

Thanks!
Pierre, Montreal

--

Dave Peterson

Using Custom Views in a protected worksheet
 

Something like this may work if the OP were using a macro to show different
views.

Smitty wrote:

You can address the protection like this:

ActiveSheet.Unprotect "password"
ActiveWorkbook.CustomViews(Target.Value).Show
ActiveSheet.Protect "password"

Sorry about neglecting that part previously.

Smitty

"Dave Peterson" wrote:

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

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

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Pierre wrote:

Smitty,

Thanks so much for the reply... except it would only be great if the problem
was that once protected we did not have access to the actual FUNCTION of
changing the views... but the problem lies in that the fact that since the
sheet IS protected, it will not allow for colums to be unhidded or hidden.

So whether I change the views traditionally or with your example, the same
problem remains... unfortunately.

If you have any other thoughts, I'll try anything.

PS: I was initially using "group" and "ungroup" but protected sheets won't
allow for ungrouping either...

:-(
--
Pierre
Montreal

"Smitty" wrote:

Here's one idea: if you have a Data Validation list that contains your Custom
View names and unprotect the cell it's in, you can use a change event. In
this case the list cell is F1:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range
Set rng = Range("J1")
' Only look at single cell changes
If Target.Count 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
ActiveWorkbook.CustomViews(Target.Value).Show
End Sub

HTH,

Smitty

"Pierre" wrote:

I want to use custom views for entering data and then printing it...

Not being the one doing the actual entering of the data, I don't want people
to inadvertantly delete colums or other info, so I need to protect the
worksheet.

My different views consist of some colums being hidden for data entry, and
then revealed for printing... but I haven't been able to make this work with
the worksheet being protected... no matter what options I check or uncheck
while protecting the sheet.

Any ideas..?

Thanks!
Pierre, Montreal

--

Dave Peterson


--

Dave Peterson

Using Custom Views in a protected worksheet
 

Thanks Smitty...! that worked...

--
Pierre
Montreal


"Dave Peterson" wrote:

Something like this may work if the OP were using a macro to show different
views.

Smitty wrote:

You can address the protection like this:

ActiveSheet.Unprotect "password"
ActiveWorkbook.CustomViews(Target.Value).Show
ActiveSheet.Protect "password"

Sorry about neglecting that part previously.

Smitty

"Dave Peterson" wrote:

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

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

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Pierre wrote:

Smitty,

Thanks so much for the reply... except it would only be great if the problem
was that once protected we did not have access to the actual FUNCTION of
changing the views... but the problem lies in that the fact that since the
sheet IS protected, it will not allow for colums to be unhidded or hidden.

So whether I change the views traditionally or with your example, the same
problem remains... unfortunately.

If you have any other thoughts, I'll try anything.

PS: I was initially using "group" and "ungroup" but protected sheets won't
allow for ungrouping either...

:-(
--
Pierre
Montreal

"Smitty" wrote:

Here's one idea: if you have a Data Validation list that contains your Custom
View names and unprotect the cell it's in, you can use a change event. In
this case the list cell is F1:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range
Set rng = Range("J1")
' Only look at single cell changes
If Target.Count 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
ActiveWorkbook.CustomViews(Target.Value).Show
End Sub

HTH,

Smitty

"Pierre" wrote:

I want to use custom views for entering data and then printing it...

Not being the one doing the actual entering of the data, I don't want people
to inadvertantly delete colums or other info, so I need to protect the
worksheet.

My different views consist of some colums being hidden for data entry, and
then revealed for printing... but I haven't been able to make this work with
the worksheet being protected... no matter what options I check or uncheck
while protecting the sheet.

Any ideas..?

Thanks!
Pierre, Montreal

--

Dave Peterson


--

Dave Peterson