Results 1 to 8 of 8

Lock cells based on value of other cells - code simplification query

Threaded View

  1. #1
    Registered User
    Join Date
    02-12-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    32

    Lock cells based on value of other cells - code simplification query

    I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.

    It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).

    Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        If [I11+J11=0] And [I11<>""] And [J11<>""] Then
            ActiveSheet.Unprotect Password:="IPG104"
            [K11:N11].Locked = True
            ActiveSheet.Protect Password:="IPG104"
        Else
            ActiveSheet.Unprotect Password:="IPG104"
            [K11:N11].Locked = False
            ActiveSheet.Protect Password:="IPG104"
        End If
        
        If [E11<>""] And [I11+J11+M11=E11] Then
            ActiveSheet.Unprotect Password:="IPG104"
            [O11:R11].Locked = True
            ActiveSheet.Protect Password:="IPG104"
        Else
            ActiveSheet.Unprotect Password:="IPG104"
            [O11:R11].Locked = False
            ActiveSheet.Protect Password:="IPG104"
        End If
    
    (all the above repeated 38 times with changes to reflect row numbers)
    
    End Sub
    Thanks.
    Last edited by glenin; 05-07-2009 at 03:13 AM.

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