+ Reply to Thread
Results 1 to 5 of 5

Locking/Unlocking sheets using a range.

  1. #1
    Forum Contributor
    Join Date
    09-02-2005
    Posts
    146

    Locking/Unlocking sheets using a range.

    Currently I'm administrating a large spreadsheet. I've built in an administrative page so I can quickly unlock and modify the workbook as needed. However, I'm always interested in making my coding more efficient.

    This is an example of something I think might be fixable:

    Private Sub Lockall()
    PW = Range("C2").Value
    Application.ScreenUpdating = False
    ActiveWorkbook.Protect (PW)
    Worksheets("Site 1").Protect (PW)
    Worksheets("Overview").Protect (PW)
    Worksheets("Site 2").Protect (PW)
    Worksheets("Site 3").Protect (PW)
    Worksheets("Site 4").Protect (PW)
    Worksheets("Site 5").Protect (PW)
    Worksheets("Site 6").Protect (PW)
    Worksheets("Site 7").Protect (PW)
    ........(Truncated).....
    Range("C4").Value = "Locked"
    Application.ScreenUpdating = True
    End Sub

    Such programming becomes tiresome when I add new sheets into the workbook because I have to modify the protect/unprotect hide/unhide codes.

    Is there a way that I can simplify it using a range? I'd think it would look something like this, but all my attempts have been met with errors.

    Private Sub Lockall()
    PW = Range("C2").Value
    Application.ScreenUpdating = False
    ActiveWorkbook.Protect (PW)
    Worksheets(range("A1":"A25")).Protect (PW)
    Range("C4").Value = "Locked"
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Harald Staff
    Guest

    Re: Locking/Unlocking sheets using a range.

    See if this get you started on something:

    Sub Lockem()
    Dim PW As String
    Dim oSht As Worksheet
    PW = "YoDaMan"
    For Each oSht In ActiveWorkbook.Worksheets
    oSht.Protect (PW)
    Next
    End Sub

    HTH. Best wishes Harald

    "wilro85" <[email protected]> skrev i
    melding news:[email protected]...
    >
    > Currently I'm administrating a large spreadsheet. I've built in an
    > administrative page so I can quickly unlock and modify the workbook as
    > needed. However, I'm always interested in making my coding more
    > efficient.
    >
    > This is an example of something I think might be fixable:
    >
    > Private Sub Lockall()
    > PW = Range("C2").Value
    > Application.ScreenUpdating = False
    > ActiveWorkbook.Protect (PW)
    > Worksheets("Site 1").Protect (PW)
    > Worksheets("Overview").Protect (PW)
    > Worksheets("Site 2").Protect (PW)
    > Worksheets("Site 3").Protect (PW)
    > Worksheets("Site 4").Protect (PW)
    > Worksheets("Site 5").Protect (PW)
    > Worksheets("Site 6").Protect (PW)
    > Worksheets("Site 7").Protect (PW)
    > .......(Truncated).....
    > Range("C4").Value = "Locked"
    > Application.ScreenUpdating = True
    > End Sub
    >
    > Such programming becomes tiresome when I add new sheets into the
    > workbook because I have to modify the protect/unprotect hide/unhide
    > codes.
    >
    > Is there a way that I can simplify it using a range? I'd think it
    > would look something like this, but all my attempts have been met with
    > errors.
    >
    > Private Sub Lockall()
    > PW = Range("C2").Value
    > Application.ScreenUpdating = False
    > ActiveWorkbook.Protect (PW)
    > Worksheets(range("A1":"A25")).Protect (PW)
    > Range("C4").Value = "Locked"
    > Application.ScreenUpdating = True
    > End Sub
    >
    >
    > --
    > wilro85
    > ------------------------------------------------------------------------
    > wilro85's Profile:

    http://www.excelforum.com/member.php...o&userid=26935
    > View this thread: http://www.excelforum.com/showthread...hreadid=572449
    >




  3. #3
    Forum Contributor
    Join Date
    09-02-2005
    Posts
    146
    Ok wait... something isn't right. For some reason this code works with .protect/unprotect but doesn't seem to have the same results with .visible. Why not and what do I need to do?

    Private Sub Unlockall()
    Application.ScreenUpdating = False
    Dim PW As String
    Dim oSht As Worksheet
    PW = Range("C2").Value
    For Each oSht In ActiveWorkbook.Worksheets
    oSht.Unprotect (PW)
    Next
    Range("C4").Value = "Unlocked"
    Application.ScreenUpdating = True
    End Sub

    Private Sub unhide()
    Application.ScreenUpdating = False
    Dim PW As String
    Dim oSht As Worksheet
    For Each oSht In ActiveWorkbook.Worksheets
    oSht.Visible = xlSheetVisible
    Next
    Application.ScreenUpdating = True
    End Sub

    **edit** Nevermind, I figured it out. The workbook was locked. D'OH!
    Last edited by wilro85; 08-17-2006 at 12:36 PM.

  4. #4
    Harald Staff
    Guest

    Re: Locking/Unlocking sheets using a range.

    Your code is fine, works well here. How do you experience that it doesn't
    work ? Is your workbook protected ? Is the workbook in question not the
    active workbook ?

    Note also that an xls file needs at least one visible sheet, so hiding all
    the same way will err.

    Best wishes Harald

    "wilro85" <[email protected]> skrev i
    melding news:[email protected]...
    >
    > Ok wait... something isn't right. For some reason this code works with
    > protect/unprotect but doesn't seem to have the same results with
    > visible. Why not and what do I need to do?
    >
    > Private Sub Unlockall()
    > Application.ScreenUpdating = False
    > Dim PW As String
    > Dim oSht As Worksheet
    > PW = Range("C2").Value
    > For Each oSht In ActiveWorkbook.Worksheets
    > oSht.Unprotect (PW)
    > Next
    > Range("C4").Value = "Unlocked"
    > Application.ScreenUpdating = True
    > End Sub
    >
    > Private Sub unhide()
    > Application.ScreenUpdating = False
    > Dim PW As String
    > Dim oSht As Worksheet
    > For Each oSht In ActiveWorkbook.Worksheets
    > oSht.Visible = xlSheetVisible
    > Next
    > Application.ScreenUpdating = True
    > End Sub
    >
    >
    > --
    > wilro85
    > ------------------------------------------------------------------------
    > wilro85's Profile:

    http://www.excelforum.com/member.php...o&userid=26935
    > View this thread: http://www.excelforum.com/showthread...hreadid=572449
    >




  5. #5
    Forum Contributor
    Join Date
    09-02-2005
    Posts
    146
    Sure enough, I ran into the "You need to have something showing, stupid" type error message. I haven't figured out the mechanics of how pages are hidden yet to figure out the best way to limit the last page to hide.

    The page that I don't want hidden is called, "Summary" this is what I've been expirmenting with.

    Private Sub reset()
    Call Unlockall
    Application.ScreenUpdating = False
    Dim PW As String
    Dim oSht As Worksheet
    On Error Resume Next
    For Each oSht In ActiveWorkbook.Worksheets
    On Worksheets("summary") GoTo ln47
    oSht.Visible = False
    Next
    'Worksheets("summary").Visible = True
    'Worksheets("summary").Select
    'Worksheets("subscriber equipment").Visible = False
    Call Lockall
    Application.ScreenUpdating = True
    MsgBox ("The form has been returned to default operation.")
    End Sub

    The parts that I've appostophied out was when I thought that it was hiding them in alphabetical order, but another time I ran the macro it ended up with another sheet unhidden. The "On Worksheets("summary") GoTo ln47" didn't work at all, but I've not played with it that much, yet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1