Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Enable/disable one cell based on another cell

    I have an Excel 2010 worksheet with three columns.

    - The cells in the "Type" column (A1:A20) have List validation (valid values are "Q" & "V").
    - The cells in the "Quantity" column (B1:B20) have Whole Number validation.
    - The cells in the "Value" column (C1:C20) have Decimal validation.

    For each row, if the Type cell contains "Q", then the Quantity cell should be enabled (locked) and the Value cell should be disabled (unlocked), or conversely if the Type cell contains "V", then the Quantity cell should be disabled (unlocked) and the Value cell should be enabled (locked).

    How can I set this up?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Murgatroyd

    The attached file should do the trick.
    ..but it doesn't require sheet protection to 'lock' cells.
    It uses 'event trapping' to detect changes made to the sheet.

    I added some conditional formatting too.

    If you really want to use locked/unlocked cells, we can do that to.

    zeddy
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply, which does what I want.

    It looks like the data entry cells have a grey background by default, which the conditional formatting changes to white to indicate when they are enabled, is that correct?

    How/where is the event trapping done?

    How would this be done with cell locking, and what are the pros/cons of the two methods?

    (My sheet currently has all non-data-entry cells locked anyway, to protect the formulae.)
    Last edited by Murgatroyd; 2016-05-05 at 22:46.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    The 'event trapping is done on the 'sheet code'
    ..point to the sheet tab name, right-click the mouse, and select 'view code'

    Yes - I set the initial background to a default grey, and used conditional formatting to: fill background 'No Color' (not to white) .

    I am testing a version using the 'cell locking' method.
    Will post it tomorrow.

    zeddy

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    OK thanks.

    I see that the first section of the code bypasses the function if the current row & column are not relevant. Can these be condensed to something like "If (zCol < 5 Or zCol > 7) Or (zRow < 10 Or zRow > 30) Then Exit Sub"?

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Murgatroyd,

    In zeddy's code, the following lines:

    Code:
    zCol = Target.Column                    'column number of cell that changed
    
    If zCol > 3 Then Exit Sub               'ignore changes beyond column 3
    
    zRow = Target.Row                       'row number of cell that changed
    
    If zRow = 1 Then Exit Sub               'ignore any changes in header row 1
    If zRow > 20 Then Exit Sub
    can be reduced to:

    Code:
    If Not Intersect(Target, Range("A2:C20")) Is Nothing Then
        'REST OF CODE GOES HERE
    I suspect that zeddy was breaking in down into simple understandable steps for you

    HTH,
    Maud

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    OK thanks. However, when I tried that, I got an error. Should it be "If Not Intersect(Target, Range("A2:C20")) Is Nothing Then Exit Sub"?

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Murgatroyd,

    The code line is correct. Look at it as if it were a double negative. Removing the variables at the beginning of the code required some slight modifications in the rest of the code. No one way is better than the other. just another way to perform zeddy's cleverness.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("A2:C20")) Is Nothing Then
    'CHANGE DETECTED IN [A2:C20]
            Application.EnableEvents = False            'turn events OFF when making changes
            Select Case Target.Column
                Case 1                                  'change detected in column [A]
                    If Target <> "V" Then
                        Cells(Target.Row, "C").ClearContents
                    End If
                    If Target <> "Q" Then
                        Cells(Target.Row, "B").ClearContents
                    End If
                Case 2                                  'change detected in column [B]
                    If Cells(Target.Row, "A") <> "Q" Then
                        Cells(Target.Row, "B").ClearContents
                    End If
                Case 3                                  'change detected in column [C]
                    If Cells(Target.Row, "A") <> "V" Then
                        Cells(Target.Row, "C").ClearContents
                    End If
                Case Else
            End Select
            Application.EnableEvents = True              'make sure event trapping is back ON
        End If
    End Sub
    Another important point when using Application.EnableEvents. As zeddy mentions, you can toggle event trapping off and on. If a change is made within a worksheet_change event, it will evoke an endless loop. So you must disable event trapping when making cell changes with code inside the change event.

    During building code and testing, you may run code that sets Application.EnableEvents to False but halts because of an error, for example, before you can turn it back on. If you try to run your code again, nothing happens. This is because event trapping is still disabled. You can turn it back on again by running a simple routine placed in a standard module

    Code:
    Public Sub Re_enableEvents()
         Application.EnableEvents = True
    end sub
    Last edited by Maudibe; 2016-05-07 at 01:51.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    OK thanks. I see now that you meant ...

    If Not Intersect(Target, Range("A2:C20")) Is Nothing Then
    'REST OF CODE GOES HERE

    ... followed by ...

    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •