Excel 2002 SP3
Win XP Pro

*Follow-up to: microsoft.public.excel.programming*

Hi,

I have a bug with the line ".EnableSelection = xlUnlockedCells" in this
macro:

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Protect Password:="nosecret"
.EnableSelection = xlUnlockedCells
End With
Next n
Application.ScreenUpdating = True
End Sub

It's always worked in the past and I can't understand what is happening now.

Be easy on me as I am a newbie at XL macros/VBA....

The "bug" shows itself in what manner?


Gord Dibben MS Excel MVP

On Tue, 09 Oct 2007 14:43:39 -0400, stef <(E-Mail Removed)> wrote:

>Excel 2002 SP3
>Win XP Pro
>
>*Follow-up to: microsoft.public.excel.programming*
>
>Hi,
>
>I have a bug with the line ".EnableSelection = xlUnlockedCells" in this
>macro:
>
>Sub ProtectAllSheets()
> Application.ScreenUpdating = False
> Dim n As Single
> For n = 1 To Sheets.Count
> With Sheets(n)
> .Protect Password:="nosecret"
> .EnableSelection = xlUnlockedCells
> End With
> Next n
> Application.ScreenUpdating = True
>End Sub
>
>It's always worked in the past and I can't understand what is happening now.
>
>Be easy on me as I am a newbie at XL macros/VBA....

Error "438':
Object doesn't support this property or method

I should have posted that, sorry.

Gord Dibben wrote:
> The "bug" shows itself in what manner?
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 09 Oct 2007 14:43:39 -0400, stef <(E-Mail Removed)> wrote:
>
>> Excel 2002 SP3
>> Win XP Pro
>>
>> *Follow-up to: microsoft.public.excel.programming*
>>
>> Hi,
>>
>> I have a bug with the line ".EnableSelection = xlUnlockedCells" in this
>> macro:
>>
>> Sub ProtectAllSheets()
>> Application.ScreenUpdating = False
>> Dim n As Single
>> For n = 1 To Sheets.Count
>> With Sheets(n)
>> .Protect Password:="nosecret"
>> .EnableSelection = xlUnlockedCells
>> End With
>> Next n
>> Application.ScreenUpdating = True
>> End Sub
>>
>> It's always worked in the past and I can't understand what is happening now.
>>
>> Be easy on me as I am a newbie at XL macros/VBA....
>

Do you have any chart sheets or such? If so then you will get the error you
discussed. Try this to see if it goes any better...

Sub ProtectAllSheets()
Dim wks As Worksheet

Application.ScreenUpdating = False
For Each wks In Worksheets
With wks
.Protect Password:="nosecret"
.EnableSelection = xlUnlockedCells
End With
Next wks
Application.ScreenUpdating = True
End Sub
--
HTH...

Jim Thomlinson


"stef" wrote:

> Error "438':
> Object doesn't support this property or method
>
> I should have posted that, sorry.
>
> Gord Dibben wrote:
> > The "bug" shows itself in what manner?
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On Tue, 09 Oct 2007 14:43:39 -0400, stef <(E-Mail Removed)> wrote:
> >
> >> Excel 2002 SP3
> >> Win XP Pro
> >>
> >> *Follow-up to: microsoft.public.excel.programming*
> >>
> >> Hi,
> >>
> >> I have a bug with the line ".EnableSelection = xlUnlockedCells" in this
> >> macro:
> >>
> >> Sub ProtectAllSheets()
> >> Application.ScreenUpdating = False
> >> Dim n As Single
> >> For n = 1 To Sheets.Count
> >> With Sheets(n)
> >> .Protect Password:="nosecret"
> >> .EnableSelection = xlUnlockedCells
> >> End With
> >> Next n
> >> Application.ScreenUpdating = True
> >> End Sub
> >>
> >> It's always worked in the past and I can't understand what is happening now.
> >>
> >> Be easy on me as I am a newbie at XL macros/VBA....
> >
>

Jim,
You are totally correct.
That must be the problem; I do have charts....
Let me try your version and post back here. Thanks.

Jim Thomlinson wrote:
> Do you have any chart sheets or such? If so then you will get the error you
> discussed. Try this to see if it goes any better...
>
> Sub ProtectAllSheets()
> Dim wks As Worksheet
>
> Application.ScreenUpdating = False
> For Each wks In Worksheets
> With wks
> .Protect Password:="nosecret"
> .EnableSelection = xlUnlockedCells
> End With
> Next wks
> Application.ScreenUpdating = True
> End Sub

It does work perfectly.

What about undoing it--the un-protect part:

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="nosecret"
Next n
Application.ScreenUpdating = True
End Sub

Now becomes:

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
For Each wks In Worksheets
With wks
Sheets(n).Unprotect Password:="nosecret"
End With
Next wks
Application.ScreenUpdating = True
End Sub

*Is that correct?*


Jim Thomlinson wrote:
> Do you have any chart sheets or such? If so then you will get the error you
> discussed. Try this to see if it goes any better...
>
> Sub ProtectAllSheets()
> Dim wks As Worksheet
>
> Application.ScreenUpdating = False
> For Each wks In Worksheets
> With wks
> .Protect Password:="nosecret"
> .EnableSelection = xlUnlockedCells
> End With
> Next wks
> Application.ScreenUpdating = True
> End Sub

I did tweak it a bit, but didn't have problems where you did.

Sub ProtectAllSheets()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ThisWorkbook.Worksheets

With WS
.Protect Password:="nosecret"
.EnableSelection = xlUnlockedCells
End With
Next WS
Application.ScreenUpdating = True
End Sub

--
HTH,
Barb Reinhardt



"stef" wrote:

> Excel 2002 SP3
> Win XP Pro
>
> *Follow-up to: microsoft.public.excel.programming*
>
> Hi,
>
> I have a bug with the line ".EnableSelection = xlUnlockedCells" in this
> macro:
>
> Sub ProtectAllSheets()
> Application.ScreenUpdating = False
> Dim n As Single
> For n = 1 To Sheets.Count
> With Sheets(n)
> .Protect Password:="nosecret"
> .EnableSelection = xlUnlockedCells
> End With
> Next n
> Application.ScreenUpdating = True
> End Sub
>
> It's always worked in the past and I can't understand what is happening now.
>
> Be easy on me as I am a newbie at XL macros/VBA....
>

Bard, same as the one Jim posted and I thank both very much for it--it
does work and makes my life easier.

Barb Reinhardt wrote:
> I did tweak it a bit, but didn't have problems where you did.
>
> Sub ProtectAllSheets()
> Dim WS As Worksheet
> Application.ScreenUpdating = False
> For Each WS In ThisWorkbook.Worksheets
>
> With WS
> .Protect Password:="nosecret"
> .EnableSelection = xlUnlockedCells
> End With
> Next WS
> Application.ScreenUpdating = True
> End Sub
>