Prevent the process of entering by code

[YasserKhalil]

I have two columns A and B...
First, I want a code in worksheet change which control the process of entering data in columns A and B.
The values have to be entered either in A1 or B1 and so on.
I want to prevent entering values in both cells just a cell of the two

 

[mikerickson]

Try putting this in the sheet's code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 And .Column < 3 Then
            With .EntireRow.Cells(3 - .Column)
                If CStr(.Value) <> vbNullString Then .Select
            End With
        End If
    End With
End Sub

Or you could change this line

If CStr(.Value) <> vbNullString Then .Offset(2 - .Column, 0).Select

 

[YasserKhalil]

thank you very much
But I want the code in worksheet_change....
Could you help me please?

 

[mikerickson]

Why do you want it in the Worksheet_Change event, it works better in SelectionChange.

The code I posted works on the principle of "if you can't select a cell, you can't enter anything into that cell". Any routine based on that priciple would fail in the Change event.

The Change event is the wrong place for code like this. It will only be triggered after the cell has changed value. Code in the Change event might correct improper entry, but it cannot prevent improper data entry.

 

[YasserKhalil]

Why do you want it in the Worksheet_Change event, it works better in SelectionChange.

The code I posted works on the principle of "if you can't select a cell, you can't enter anything into that cell". Any routine based on that priciple would fail in the Change event.

The Change event is the wrong place for code like this. It will only be triggered after the cell has changed value. Code in the Change event might correct improper entry, but it cannot prevent improper data entry.

you are right
thanks again for your great help

I want the code to include the range A2:B20 only as A1 and B1 will be headers..

 

[mikerickson]

If Not Application.Intersect(Target, Range("A2:B20")) Is Nothing Then
    Rem run code
End If

 

[YasserKhalil]

If Not Application.Intersect(Target, Range("A2:B20")) Is Nothing Then
    Rem run code
End If

thanks thanks thanks thanks thanks a lot
you are very helpful