Keep ID Number With Customer

L

Legacy 436357

Guest
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
ABCDEFGHIJ
1IDContact NameCompany NameStreet AddressCitySTZip CodePhoneEmailNotes
2101JohnBank12 Main StreetWeatherford76087(999) 999-9999john@gmail.comnew customer
3102
Customers
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
With VBA :
Code:
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
 
Last edited:
Upvote 0
Revised :
Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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 :
Code:
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.
 
Upvote 0
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 :
Code:
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
 
Upvote 0
I forgot to mention to delete the Worksheet_SelectionChange procedure.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top