none
a cell locked after input in another cell RRS feed

  • Question

  • I need VB code to use in Excel as below:

    If i input in cell A1 the cell B1 is locked and if I input in cell B1 the cell A1 is locked if both cell no value they will not be locked.

    Please help me.

    Thanks

    Saturday, June 25, 2016 10:04 AM

All replies

  • Do this first:

    1. Select A1:B1, and all other cells that the user should be able to edit.
    2. Press Ctrl+1 to activate the Format Cells dialog.
    3. Activate the Protection tab.
    4. Clear the Locked check box.
    5. Click OK.
    6. Activate the Review tab of the ribbon.
    7. Click Protect Sheet.
    8. Specify a password if you wish, then click OK.

    Now for the code.

    1. Right-click the sheet tab.
    2. Select View Code from the context menu.
    3. Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("A1"), Target) Is Nothing Then
            Application.EnableEvents = False
            Me.Unprotect ' Password:="secret"
            Range("B1").Locked = (Range("A1").Value <> "")
            Me.Protect ' Password:="secret"
            Application.EnableEvents = True
        End If
        If Not Intersect(Range("B1"), Target) Is Nothing Then
            Application.EnableEvents = False
            Me.Unprotect ' Password:="secret"
            Range("A1").Locked = (Range("B1").Value <> "")
            Me.Protect ' Password:="secret"
            Application.EnableEvents = True
        End If
    End Sub

    Switch back to Excel, and don't forget to save the workbook in a macro-enabled format (.xlsm, .xlsb or .xls).

    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, June 25, 2016 11:10 AM