Keep ID Number With Customer

[Legacy 436357]

Hello,

Is there a way to keep the assigned ID with each customer ? I want to be able to sort the table but when doing so the ID numbers for the customers change.

Thank you very much

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	ID	Contact Name	Company Name	Street Address	City	ST	Zip Code	Phone	Email	Notes
2	101	John	Bank	12 Main Street	Weatherford		76087	(999) 999-9999	john@gmail.com	new customer
3	102
Customers

 

[footoo]

With VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B:B]) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target = "" Then
    Target(1, 0).ClearContents
Else
    Target(1, 0) = Target.Row + 100
End If
Application.EnableEvents = True
End Sub

 

[footoo]

Revised :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B:B]) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target = "" Then
    Target(1, 0).ClearContents
Else
    Target(1, 0) = Target.Row + 100
End If
If WorksheetFunction.CountIf([A:A], Target(1, 0)) > 1 Then
    MsgBox "ID " & Target(1, 0) & " already exists."
    Target(1, 0).ClearContents
End If
Application.EnableEvents = True
End Sub

 

[Legacy 436357]

Thank you that seems to work. Is there a way to protect column A so it can't be altered by the user but also allow the table to add rows for new records?

 

[Legacy 436357]

I will figure that part out thanks for the help.

 

[footoo]

Thank you that seems to work. Is there a way to protect column A so it can't be altered by the user but also allow the table to add rows for new records?

Try this :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [A:A]) Is Nothing Then
    Application.EnableEvents = False
    Cells(Target.Row, "B").Select
    Application.EnableEvents = True
End If
End Sub

The two procedures posted do not prevent a value being dragged into column A from another column.
If this a problem, post again - the Worksheet_Change procedure would need to be revised.

 

[footoo]

The two procedures posted do not prevent a value being dragged into column A from another column.
If this a problem, post again - the Worksheet_Change procedure would need to be revised.

Here it is :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [A:A]) Is Nothing Then
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
    Exit Sub
End If
Dim cel As Range
Dim rng As Range: Set rng = Intersect(Target, [B:B])
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each cel In rng
        If cel = "" Then
            cel(1, 0).ClearContents
        Else
            cel(1, 0) = cel.Row + 100
        End If
        If WorksheetFunction.CountIf([A:A], cel(1, 0)) > 1 Then
            MsgBox "ID " & cel(1, 0) & " already exists."
            cel(1, 0).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

 

[footoo]

I forgot to mention to delete the Worksheet_SelectionChange procedure.