CODEWITHSUNDEEP
excelvba
Bob Rob
Bob Rob

Reputation: 164

How can I allow changing a cell only when another cell has a specific value?

I have an Excel file where all the cells of column A contain a numerical value, and column B, which contains other values.

I would like with VBA (or other options, if there are alternatives) to control the change of the values in column A.

The 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.

Upvotes: 0

Views: 430

Answers (3)

VBasic2008
VBasic2008

Reputation: 54797

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

Upvotes: 0

FaneDuru
FaneDuru

Reputation: 42236

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

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

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

Upvotes: 0

Related Questions