Preventing dynamically updated lists from deletion

[robert41]

Hi this forum helped me big time the other day and I have some follow up questions, especially for jindon if available. Although the last fix works perfectly, I now realize that if I delete or purge the names in column A of the Loan Records worksheet, that the dynamically updated names in the Customer Record worksheet will also be deleted..and I don't want that!

Is there a way to prevent the customer names in the Customer Record worksheet from being deleted if the names in column A of the Loan Record worksheet are deleted?

Also, I would like to protect the Loan Record columns with blue font color from being editable but the vba code that auto sorts and updates the Customer Record worksheet gives me an error when I put the protection on the Loan Record worksheet. Is there any way to fix that?

Lastly, when I try to assign a macro to a smart shape, I get an error and excel crashes even though the macros works fine using the keyboard shortcuts seen in the Loan Record worksheet. You can see all these issues in the attached sample workbook. Thanks again.LenFrenBackup.xlsm

[JBeaucaire]

Click on the Developer tab and turn on the Design Mode icon, this turns off macros until you turn that mode off again, thus reenabling macros again.

http://screencast.com/t/vr5P5jaH

[robert41]

Click on the Developer tab and turn on the Design Mode icon, this turns off macros until you turn that
mode off again, thus reenabling macros again.

Thank you JBeaucaire for your response, that's a neat little trick that will come in handy going forward. Maybe I can approach the deletion issue from another angle.

Jindon gave me this awesome code that auto inserts/deletes and auto sorts customer names in a database in the 'Customer Record' worksheet that were first created in the 'Loan Record' worksheet (here's the workbook :LenFrenBackup.xlsm). Is there anyway to tweak this code to auto insert a new customer's name only and not to auto delete? Here is the code:

Private Sub Worksheet_Activate()
    Dim a, i As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("Loan Record").Range("a5").CurrentRegion _
        .Columns(1).Value
    For i = 3 To UBound(a, 1)
        If Trim(a(i, 1)) <> "" Then dic(Trim$(a(i, 1))) = Empty
    Next
    a = Range("a1").CurrentRegion.Columns(1).Value
    For i = UBound(a, 1) To 2 Step -1
        If dic.exists(Trim$(a(i, 1))) Then
            dic.Remove Trim$(a(i, 1))
        Else
            Rows(i).Delete
        End If
    Next
    If dic.Count > 0 Then
        Range("a" & Rows.Count).End(xlUp)(2).Resize(dic.Count).Value = _
        Application.Transpose(dic.keys)
        Range("a1").CurrentRegion.Sort _
            key1:=Range("a1"), order1:=1, Header:=xlYes
    End If
    Set dic = Nothing
End Sub

[robert41]

Problem solved. I figured it out using a bit of reverse engineering...thanks again everyone for your help.