Results 1 to 9 of 9

Locked worksheet based upon date and locked columns when worksheet is not locked

Threaded View

ruralbrew Locked worksheet based upon... 05-04-2011, 09:15 AM
realniceguy5000 Re: Locked worksheet based... 05-04-2011, 10:43 AM
ruralbrew Re: Locked worksheet based... 05-04-2011, 03:49 PM
realniceguy5000 Re: Locked worksheet based... 05-04-2011, 05:07 PM
ruralbrew Re: Locked worksheet based... 05-04-2011, 05:15 PM
ruralbrew Re: Locked worksheet based... 05-09-2011, 02:53 PM
realniceguy5000 Re: Locked worksheet based... 05-09-2011, 05:18 PM
  1. #1
    Registered User
    Join Date
    05-04-2011
    Location
    Syracuse, NY, USA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Locked worksheet based upon date and locked columns when worksheet is not locked

    I am creating a spreadsheet with multipl worksheets (12 to be exact, one for each month). On the 7th of the month after the worksheet locks for user editing. (2/7/2011 the January worksheet was locked). I do this by protecting all the sheets before close and then at open call for protectsheet to do a date check and unprotect the necessary tables. In the unprotected sheets I need to lock columns E, H, K and L. I am wondering is I should change a code or add something to do this and I can't figure out what to do. Any help would be greatly appreciated.

    Workbook code currently being used:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Jan").Protect Password:="Secret"
    Sheets("Feb").Protect Password:="Secret"
    Sheets("Mar").Protect Password:="Secret"
    Sheets("Apr").Protect Password:="Secret"
    Sheets("May").Protect Password:="Secret"
    Sheets("Jun").Protect Password:="Secret"
    Sheets("Jul").Protect Password:="Secret"
    Sheets("Aug").Protect Password:="Secret"
    Sheets("Sep").Protect Password:="Secret"
    Sheets("Oct").Protect Password:="Secret"
    Sheets("Nov").Protect Password:="Secret"
    Sheets("Dec").Protect Password:="Secret"
    End Sub
    
    Private Sub Workbook_Open()
    Call ProtectSheet
    End Sub
    
    Sub ProtectSheet()
        If Now() < DateSerial(2011, 2, 7) Then
           
                Sheets("Jan").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 3, 7) Then
           
                Sheets("Feb").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 4, 7) Then
           
                Sheets("Mar").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 5, 7) Then
           
                Sheets("Apr").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 6, 7) Then
           
                Sheets("May").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 7, 7) Then
           
                Sheets("Jun").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 8, 7) Then
           
                Sheets("Jul").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 9, 7) Then
           
                Sheets("Aug").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 10, 7) Then
           
                Sheets("Sep").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 11, 7) Then
           
                Sheets("Oct").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2011, 12, 7) Then
           
                Sheets("Nov").Unprotect Password:="Secret"
    
       
        End If
        If Now() < DateSerial(2012, 1, 7) Then
           
                Sheets("Dec").Unprotect Password:="Secret"
    
       
        End If
    End Sub
    Attached Files Attached Files
    Last edited by ruralbrew; 05-04-2011 at 03:48 PM.

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