Protecting sheets in Excel 2003, 2007 and 2010

**kensei** · 01-14-2011, 05:23 AM

hi guys, some of you knows if in different version of excel the protection VBA code is the same ?

any differences ?

i have a class module called Data manager in which i have this function

Private Sub ProtectAllSheets(ByVal block As Boolean, Optional ByVal onlySystem As Boolean)
    On Error Resume Next

    If (onlySystem) Then
        If (block) Then
            Call Sheets("system").Protect
            Call Sheets("s2").Protect(SHEET_PROTECT)
            Call Sheets("s3").Protect(SHEET_PROTECT)
            Call Sheets("s4").Protect(SHEET_PROTECT)
            Call Sheets("s5").Protect(SHEET_PROTECT)
        Else
            Call Sheets("s2").Unprotect(SHEET_PROTECT)
            Call Sheets("23").Unprotect(SHEET_PROTECT)
            Call Sheets("24").Unprotect(SHEET_PROTECT)
            Call Sheets("25").Unprotect(SHEET_PROTECT)
        End If
    Else
        Dim sheet As Worksheet
    
        For Each sheet In Sheets
            If (block) Then
                If (loginStatus = LOGGED_AS_ADMIN) Then
                    Call sheet.Protect(SHEET_PROTECT, AllowInsertingRows:=True, AllowDeletingRows:=True)
                ElseIf (loginStatus = LOGGED_IN And ReturnCellStatus(sheet)) Then
                    Call sheet.Protect(SHEET_PROTECT, AllowInsertingRows:=True, AllowDeletingRows:=True)
                Else
                    Call sheet.Protect(SHEET_PROTECT, AllowInsertingRows:=False, AllowDeletingRows:=False)
                End If
            Else
                Call sheet.Unprotect(SHEET_PROTECT)
            End If
        Next
    End If
End Sub

and it works fine in excel 2003 but when it runs on excel 2007 and excel 2010 cause some problem in the sheet content. some chart and button changes their position when the sheet is protected.. even if theese object are linked to a specific cell...

anyone who can help me ?

bye and thanks :D

**royUK** · 01-14-2011, 05:24 AM

Protection remains the same in all versions afaik

**kensei** · 01-14-2011, 05:57 AM

Originally Posted by royUK

Protection remains the same in all versions afaik

Thanks

so using

call sheet("bla").protect("password")

is the same as

sheet("bla").protect password:="password"

?

and the last thing :P how can i avoid that oject such as chart and buttons changest their porition after protection ?

thanks :D

**royUK** · 01-14-2011, 06:03 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**kensei** · 01-14-2011, 06:06 AM

done! :D

sorry i thought that for a single line could be omitted ^_^

**royUK** · 01-14-2011, 06:12 AM

We like all code snippets to be displayed with tags.

Call is used to run a Procedure or Macro, so the second line of code should work. You can declare the password as a Constant

Const PW as String = "secret"

sheet("bla").protect password:= PW

I'm not sure why the objects are moving maybe attach the workbook in a new thread on that problem

**kensei** · 01-14-2011, 06:39 AM

Originally Posted by royUK

We like all code snippets to be displayed with tags.

Call is used to run a Procedure or Macro, so the second line of code should work. You can declare the password as a Constant

Const PW as String = "secret"

sheet("bla").protect password:= PW

I'm not sure why the objects are moving maybe attach the workbook in a new thread on that problem

yep the pdw is delared as constant,
i posted the password directly to keep the code short :D

i posted the element position problem here i don't know if it is the correct section: http://www.excelforum.com/excel-new-...to-2010-a.html some day ago but only yesterday i start thinking that the problem could be protection ...

thanks roy