Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 23

Thread: Conditional Locking Of Cells

  1. #1
    Join Date
    1st September 2007
    Posts
    10

    Conditional Locking Of Cells

    I have a condition whereby if cell C7 has a value entered, then cells D7 & E7 cannot have values entered. Like wise, if cells D7 & E7 have values, them cell C7 cannot. Cell F7 would be the product of the calculations using the data either in only C7 or only in D7 & E7. Please see the sample attached worksheet with my problem elaborated. Thanks so much for any help.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Conditional Locking Of Cells

    The formula for F7 is

    =IF(C7<>0, C7*B7, (D7 * B10) * 0.75 + (E7 * B10) * 0.25 )

    Then add conditional formatting:

    for C7: Cell value is = 0 (and format w/ hatch pattern)
    for D7 and C7: Formula is =$C7<>0 (and format w/ hatch pattern).
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  3. #3
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,921

    Re: Conditional Locking Of Cells

    Kenp7,

    This was a duplicate thread, I have deleted the other one.

    Do you want to actually lock the cells?

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

  5. #5
    Join Date
    1st September 2007
    Posts
    10

    Re: Conditional Locking Of Cells

    Quote Originally Posted by Reafidy
    Kenp7,

    This was a duplicate thread, I have deleted the other one.

    Do you want to actually lock the cells?

    Yes, I need to lock C7 if D7 & E7 have values. Likewise, I need to lock D7 & E7 if C7 has a value. Thanks so much!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,349

    Re: Conditional Locking Of Cells

    Assuming your sheet is protected and C7:E7 start off being empty and unlocked, then the following code should work. It will be defeated if a user enters values in more than one cell at a time. The code must be placed in the sheet module (right click on the sheet tab and select View Code).
    VB:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
        Me.Unprotect 
        With Target.Cells(1) 
            If .Address = "$C$7" Then 
                Range("D7:E7").Locked = (Len(.Value) > 0) 
            ElseIf .Address = "$D$7" Or .Address = "$E$7" Then 
                Range("C7").Locked = Not (IsEmpty(Range("D7")) And IsEmpty(Range("E7"))) 
            End If 
        End With 
        Me.Protect 
    End Sub 
    
    
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

  7. #7
    Join Date
    1st September 2007
    Posts
    10

    Re: Conditional Locking Of Cells

    I tried it and it worked only if I first entered a value in C7. If I start out with D7, it automatically (apparently) protected or locked that cell disallowing any value to be entered.
    I pasted the code and double checked it for accuracy but no luck. Sorry if I failed to do something here.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,349

    Re: Conditional Locking Of Cells

    I just now tried the macro again and it worked as it should even when I started with D7. Did you start with the three cells C7:E7 unlocked?

    If it's still not working, please attache the workbook with the problem so it can be checked to see what went wrong.
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

  9. #9
    Join Date
    1st September 2007
    Posts
    10

    Re: Conditional Locking Of Cells

    Thanks for your continued patience. Attached please see my worksheet. Please understand, this worksheet will be used by my potential customers to compare one service with another and as such, will be making multiple changes, starting over, etc. The worksheet also needs to be protected so no one can make any code or formula changes. The protection password is "nutrient"
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th July 2007
    Posts
    759

    Re: Conditional Locking Of Cells

    It looks like the 3 cells in question (C7:E7) may need to be empty in order for this work. If all 3 are empty to start, the locking works as it should. If all 3 are set to 0 to start, it only works when changes are made to C7.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Conditional Cell Locking & Protection
    By jamboss in forum EXCEL HELP
    Replies: 1
    Last Post: June 25th, 2008, 21:30
  2. Conditional Cell locking
    By Steve at work in forum EXCEL HELP
    Replies: 2
    Last Post: July 25th, 2006, 14:42
  3. locking certain cells
    By fastballfreddy in forum EXCEL HELP
    Replies: 1
    Last Post: May 8th, 2006, 15:02
  4. Inadvertantly locking all cells
    By JJacob in forum EXCEL HELP
    Replies: 2
    Last Post: January 9th, 2006, 18:59
  5. Locking unlocked cells
    By Field8585 in forum EXCEL HELP
    Replies: 4
    Last Post: September 2nd, 2005, 02:13

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
  •  
porno