Results 1 to 1 of 1

VBA Code to lock and unlock cells.

Threaded View

  1. #1
    Registered User
    Join Date
    11-08-2010
    Location
    Jacksonville Fl
    MS-Off Ver
    Excel 2007
    Posts
    1

    Unhappy VBA Code to lock and unlock cells.

    My problem is a have drop down box where the user selects a certain number of boxes to input data (example 3) and the code should prevent the user from entering data into all remaining boxes except the first 3. The code works if I select say box 4 or 5 before selecting box 3. However, after I select box 3 all of my boxes unlock and the use can now enter data in boxes 4 and 5. My code is below. Please advise where my logic has gone arrye
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        ActiveSheet.Unprotect Password:="pass"
       
        
        If Target.Address(False, False) = "C6" Then
        Select Case Target.Value
            
        Case 1: Range("Borrower1_Income").Select
            Selection.ClearContents
            Range("Borrower2_Income").Select
            Selection.ClearContents
            Range("Borrower3_Income").Select
            Selection.ClearContents
            Range("Borrower4_Income").Select
            Selection.ClearContents
            Range("Borrower5_Income").Select
            Selection.ClearContents
            Range("Borrower6_Income").Select
            Selection.ClearContents
            
        Range("Borrower1_Income").Locked = False
        Range("Borrower2_Income").Locked = True
        Range("Borrower3_Income").Locked = True
        Range("Borrower4_Income").Locked = True
        Range("Borrower5_Income").Locked = True
        Range("Borrower6_Income").Locked = True
    
        ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFiltering:=True
        
        Case 2: Range("Borrower1_Income").Select
            Selection.ClearContents
            Range("Borrower2_Income").Select
            Selection.ClearContents
            Range("Borrower3_Income").Select
            Selection.ClearContents
            Range("Borrower4_Income").Select
            Selection.ClearContents
            Range("Borrower5_Income").Select
            Selection.ClearContents
            Range("Borrower6_Income").Select
            Selection.ClearContents
            
    Range("Borrower1_Income").Locked = False
    Range("Borrower2_Income").Locked = False
    Range("Borrower3_Income").Locked = True
    Range("Borrower4_Income").Locked = True
    Range("Borrower5_Income").Locked = True
    Range("Borrower6_Income").Locked = True
            
        ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFiltering:=True
            
        Case 3: Range("Borrower1_Income").Select
            Selection.ClearContents
            Range("Borrower2_Income").Select
            Selection.ClearContents
            Range("Borrower3_Income").Select
            Selection.ClearContents
            Range("Borrower4_Income").Select
            Selection.ClearContents
            Range("Borrower5_Income").Select
            Selection.ClearContents
            Range("Borrower6_Income").Select
            Selection.ClearContents
            
    Range("Borrower1_Income").Locked = False
    Range("Borrower2_Income").Locked = False
    Range("Borrower3_Income").Locked = False
    Range("Borrower4_Income").Locked = True
    Range("Borrower5_Income").Locked = True
    Range("Borrower6_Income").Locked = True
            
        ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFiltering:=True
            
            
            Case 4: Range("Borrower1_Income").Select
            Selection.ClearContents
            Range("Borrower2_Income").Select
            Selection.ClearContents
            Range("Borrower3_Income").Select
            Selection.ClearContents
            Range("Borrower4_Income").Select
            Selection.ClearContents
            Range("Borrower5_Income").Select
            Selection.ClearContents
            Range("Borrower6_Income").Select
            Selection.ClearContents
            
    Range("Borrower1_Income").Locked = False
    Range("Borrower2_Income").Locked = False
    Range("Borrower3_Income").Locked = False
    Range("Borrower4_Income").Locked = False
    Range("Borrower5_Income").Locked = True
    Range("Borrower6_Income").Locked = True
            
        ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFiltering:=True
            
            
            Case 5: Range("Borrower1_Income").Select
            Selection.ClearContents
            Range("Borrower2_Income").Select
            Selection.ClearContents
            Range("Borrower3_Income").Select
            Selection.ClearContents
            Range("Borrower4_Income").Select
            Selection.ClearContents
            Range("Borrower5_Income").Select
            Selection.ClearContents
            Range("Borrower6_Income").Select
            Selection.ClearContents
            
    Range("Borrower1_Income").Locked = False
    Range("Borrower2_Income").Locked = False
    Range("Borrower3_Income").Locked = False
    Range("Borrower4_Income").Locked = False
    Range("Borrower5_Income").Locked = False
    Range("Borrower6_Income").Locked = True
            
        ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFiltering:=True
            
            
            Case 6: Range("C15:C16,C19,C21:C33,F15:F16,F19,F21:F33,I15:I16,I19,I21:I33,C39:C40,C43,C45:C57,F39:F40,F43,F45:F57,I39:I40,I43,I45:I57").Select
            Selection.ClearContents
            
    Range("Borrower1_Income").Locked = False
    Range("Borrower2_Income").Locked = False
    Range("Borrower3_Income").Locked = False
    Range("Borrower4_Income").Locked = False
    Range("Borrower5_Income").Locked = False
    Range("Borrower6_Income").Locked = False
            
        ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFiltering:=True
            
            
            Range("a1").Select
        End Select
        
        
    End If
    
    
    
    
    
    End Sub

    Moderator's Note: Welcome to the forum. Nice to have you with us. Btw when posting codes be sure to enclosed it with code tags. Highlight the codes then select the "#" symbol. Thank you.
    Last edited by vlady; 08-26-2013 at 07:38 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Code to grey out and lock/unlock certain cells
    By Glensafro in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2012, 11:59 AM
  2. Lock and Unlock Cells with VBA
    By gelinasc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-26-2012, 09:40 PM
  3. using VB code to unlock and lock different areas on multiple sheets
    By Healthwatch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2010, 11:12 PM
  4. Unlock/Lock VBProject through code?
    By djvice in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2005, 05:54 PM
  5. [SOLVED] Lock and Unlock cells using VBA
    By Peter in forum Excel General
    Replies: 1
    Last Post: 01-29-2005, 10:06 AM

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