Announcement

Collapse
No announcement yet.

Conditional Locking Of Cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      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?
      Reafidy

      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

      Comment


      • #4
        Re: Conditional Locking Of Cells

        kenp7, there are lots of answers above your 1st post.

        Comment


        • #5
          Re: Conditional Locking Of Cells

          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!

          Comment


          • #6
            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).
            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.

            Comment


            • #7
              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.

              Comment


              • #8
                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.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: Conditional Locking Of Cells

                      The point of all three cells being empty had merit and I tried it and it worked...once. Since this will be used to make multiple comparisons, it is necessary to cancel out the initial values and start over. The condition works only once to the extent I can not delete the initial values to start over or make a change. The reason is the code is apparently causing the worksheet to either protect or lock the other cells after the initial value input? Is it possible this cannot be done?

                      Comment


                      • #12
                        Re: Conditional Locking Of Cells

                        Your attached workbook works fine. To start over just delete any values that may be in C7:E7, and begin again. You may want to have a "Reset" Button to do that automatically. If so, the code to attach to the button might be
                        Sub Reset()
                        Me.Unprotect
                        Range("C7:E7").ClearContents
                        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.

                        Comment


                        • #13
                          Re: Conditional Locking Of Cells

                          Thanks for your suggestions. This has become very humbling to me. You state, to start over, just delete any values in C7:E7, and that is exactly the problem. It should be assumed that someone using this worksheet would indeed want to start over or make a correction of a cell value...but that can't be done. Once a value is entered into C7, for example, that value can not be deleted because that cell is now protected! The same holds true for the other cells. And once this happens, the user (my customer) now has an analysis spreadsheet that he can no longer use because the values can not be deleted. Your comment of a reset button would probably not be effective for my customer who probably knows absolutely nothing about Excel.
                          Oh, Woe is me !!???

                          Comment


                          • #14
                            Re: Conditional Locking Of Cells

                            The sheets works fine for me (and apparently for derk as well). I can enter and delete data in the 3 cells in question, and after each change the appropriate cells are locked.

                            Comment


                            • #15


                              Re: Conditional Locking Of Cells

                              Of one thing I am absolutely certain; You gentlemen know what you're doing. The problem, no doubt, is me. In one last attempt (I promise) for me to understand all this, I have attached my worksheet for your final review. Could you please confirm that I've done all you have suggested (code, formula, etc.). I hope it's not too much to ask you to then resend the worksheet back to me. You guys are paragons of patience.

                              Comment

                              Working...
                              X