Protect/Unprotect w/ VBA not working as supposed...

Hello,

My protecting Excel 97 VBA-code is as follows
With ActiveWorkbook.Sheets("MAIN")
.EnableSelection = xlUnlockedCells
.EnableAutoFilter = True
.Protect Contents:=True, userInterfaceOnly:=True

One of the Unprotect codes is
Sheets("Main").Select
ActiveSheet.Unprotect

....but

....after unprotect, copy/paste is not possible. What can be wrong with the
protect code? How can I restore the complete unprotect state?

HBj

Is the Unprotect line in the same macro AND preceeding the Protect line?

If so, is it possible that the code is being bypassed by
Application.EnableEvents = False

What happens if you place a breakpoint on the Protect line and
then run the sub? Does the code break on this line? If not,
your code is skipping over this area.

"HBj" wrote:

> Hello,
>
> My protecting Excel 97 VBA-code is as follows
> With ActiveWorkbook.Sheets("MAIN")
> .EnableSelection = xlUnlockedCells
> .EnableAutoFilter = True
> .Protect Contents:=True, userInterfaceOnly:=True
>
> One of the Unprotect codes is
> Sheets("Main").Select
> ActiveSheet.Unprotect
>
> ....but
>
> ....after unprotect, copy/paste is not possible. What can be wrong with the
> protect code? How can I restore the complete unprotect state?
>
> HBj
>
>
>

Sub UnProtect()
Dim i As Integer
Dim SheetCount As Integer
Dim SheetName As String

SheetCount = ActiveWorkbook.Sheets.Count

For i = 1 To SheetCount
SheetName = ActiveWorkbook.Sheets(i).Name
ActiveWorkbook.Sheets(i).UnProtect
Next i
End Sub

Sub Protect()
Dim i As Integer
Dim SheetCount As Integer
Dim SheetName As String

SheetCount = ActiveWorkbook.Sheets.Count

For i = 1 To SheetCount
SheetName = ActiveWorkbook.Sheets(i).Name
ActiveWorkbook.Sheets(i).Protect
ActiveSheet.EnableSelection = xlUnlockedCells
Next i
End Sub

"HBj" wrote:

> Hello,
>
> My protecting Excel 97 VBA-code is as follows
> With ActiveWorkbook.Sheets("MAIN")
> .EnableSelection = xlUnlockedCells
> .EnableAutoFilter = True
> .Protect Contents:=True, userInterfaceOnly:=True
>
> One of the Unprotect codes is
> Sheets("Main").Select
> ActiveSheet.Unprotect
>
> ....but
>
> ....after unprotect, copy/paste is not possible. What can be wrong with the
> protect code? How can I restore the complete unprotect state?
>
> HBj
>
>
>

I tested those two subs, saved the file to a new file and tested once more:
Edit/Paste remains grayed. I do not know much about the functioning of the
protection command options used, but I'm afraid that the options I've used
should be cancelled by another function than just the plain .Unprotect. Am I
right?

HBj

"asmenut" <[email protected]> wrote in message
news:[email protected]...
> Sub UnProtect()
> Dim i As Integer
> Dim SheetCount As Integer
> Dim SheetName As String
>
> SheetCount = ActiveWorkbook.Sheets.Count
>
> For i = 1 To SheetCount
> SheetName = ActiveWorkbook.Sheets(i).Name
> ActiveWorkbook.Sheets(i).UnProtect
> Next i
> End Sub
>
> Sub Protect()
> Dim i As Integer
> Dim SheetCount As Integer
> Dim SheetName As String
>
> SheetCount = ActiveWorkbook.Sheets.Count
>
> For i = 1 To SheetCount
> SheetName = ActiveWorkbook.Sheets(i).Name
> ActiveWorkbook.Sheets(i).Protect
> ActiveSheet.EnableSelection = xlUnlockedCells
> Next i
> End Sub
>
> "HBj" wrote:
>
> > Hello,
> >
> > My protecting Excel 97 VBA-code is as follows
> > With ActiveWorkbook.Sheets("MAIN")
> > .EnableSelection = xlUnlockedCells
> > .EnableAutoFilter = True
> > .Protect Contents:=True, userInterfaceOnly:=True
> >
> > One of the Unprotect codes is
> > Sheets("Main").Select
> > ActiveSheet.Unprotect
> >
> > ....but
> >
> > ....after unprotect, copy/paste is not possible. What can be wrong with
the
> > protect code? How can I restore the complete unprotect state?
> >
> > HBj
> >
> >
> >