I have an excel file, all the cells of the column A contain a value (numerical value), then there is the column B, which contains other values.

I would like to write a script in vba (or also other options, if there are alternatives) to control the change the values in column A.

To be clear, the excel user can change the cell A1 only if the cell B1 has a value equal to zero. The same for the following rows, so the row A2 can be changed only if B2 is equal to zero, and so on.

I have searched on internet but I can't find a good solution, in particular because this must work on a large number of cells.

Thanks

CodePudding user response：

You can use Data validation for this:

Select the range (ex: A1:A10)

Go to Data -> Data tools -> Data Validation

Choose the following options:

Allow "Custom"

Formula: =B1:B10=0

CodePudding user response：

Please, copy the next event code in the respective sheet code module, and play with changing:

Option Explicit

Private newVal
Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.cells.count > 1 Then Exit Sub
        If Target.column = 1 Then
            If Target.Offset(, 1).Value <> 0 Then
                Application.EnableEvents = False 'to avoind triggering again the Change event
                  newVal = Target.Value
                  Application.Undo
                Application.EnableEvents = True
            End If
        End If
End Sub

CodePudding user response：

A Worksheet Selection Change: Restrict Column Access

• With the current setup (A2, B), the following will allow access to column A (more accurately, to A2:A1048576) only if a single cell is to be selected and the cell in the same row of column B is equal to 0.
• Optionally, if you choose the out-commented If statement, it will also allow access if the cell in column B is empty.
• An empty cell is also numeric and equal to 0. It isn't of type Double though, but of type Empty.

Sheet Module e.g. Sheet1 (Not Into ThisWorkbook or Standard Module e.g. Module1)

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Const TGT_FIRST_CELL As String = "A2"
    Const SRC_COLUMN As String = "B"
    
    Dim trg As Range
    With Me.Range(TGT_FIRST_CELL)
        Set trg = .Resize(Me.Rows.Count - .Row   1)
    End With
    
    Dim irg As Range: Set irg = Intersect(trg, Target)
    If irg Is Nothing Then Exit Sub
    
    Dim ColumnOffset As Long
    ColumnOffset = Me.Columns(SRC_COLUMN).Column - irg.Column
    
    Dim srg As Range: Set srg = irg.Offset(, ColumnOffset)
    
    If srg.Cells.Count = 1 Then
        Dim sValue As Variant: sValue = srg.Value
        If VarType(sValue) = vbDouble Then ' write just if 0
        ' or:
        'If IsNumeric(sValue) Then ' write if 0 or empty
            If sValue = 0 Then Exit Sub
        End If
    End If
    
    srg.Cells(1).Select
    
End Sub