DataNumen CRM – A Free Customer Relationship Management System in Your Excel

A happy customer is very hard to attain and retain. If you are struggling to maintain your Clients’ database, with this tool you can easily add new Client info, edit or delete existing Client info and retrieve them quickly.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

Create 3 sheets and name it as “Entry”, “Client” and “Follow-up”. On the sheet “Entry” create fields, headers, and buttons.

Let’s make it functional

Copy the macro to a new macro enabled workbook and add it to a new module. Attach the macro “Add_Client” to the button “Add”. The macro “Upldate_Client” should be attached to the button “Update” and the macro “Follow_Up” to the button “Submit”

Sub Add_client()
    Dim lr As Long
    Dim r As Long
    lr = client.Range("A" & Rows.Count).End(xlUp).Row + 1
    client.Range("A" & lr).Value = entry.Range("B6").Value
    client.Range("B" & lr).Value = entry.Range("B9").Value
    client.Range("C" & lr).Value = entry.Range("B12").Value
    client.Range("D" & lr).Value = entry.Range("B15").Value
    client.Range("E" & lr).Value = entry.Range("B18").Value
    Call Module1.Clear_Add_Client
End Sub

Sub Clear_Add_Client()
    Range("B6:F6,B9:F9,B12:F12,B15:F15,B18:F18").Select
    Range("B18").Activate
    Selection.ClearContents
    Range("B6:F6").Select
End Sub

Sub p_client_dropdown()
    On Error Resume Next
    Sheets("Temp").Delete
    Sheets.Add.Name = "Temp"
    On Error GoTo 0
    client.Select
    client.Columns("A:A").Select
    Selection.Copy
    Sheets("Temp").Select
    Sheets("Temp").Paste
    Application.CutCopyMode = False
    Sheets("Temp").Range("$A$1:$A$10000").RemoveDuplicates Columns:=1, Header:=xlYes
    Dim lr As Long
    Dim r As Long
    lr = Sheets("Temp").Range("A" & Rows.Count).End(xlUp).Row
    Dim prodlist As String
    For r = 2 To lr
        If prodlist = "" Then
            prodlist = Sheets("Temp").Range("A" & r).Value
        Else
            prodlist = prodlist & "," & Sheets("Temp").Range("A" & r).Value
        End If
        Next r
        With entry.Range("H6").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=prodlist
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        With entry.Range("N6").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=prodlist
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
End Sub
    
Sub Update_Client()
    Dim lr As Long
    lr = client.Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        If client.Range("A" & r).Value = entry.Range("H6").Value Then
            client.Range("B" & r).Value = entry.Range("H9").Value
            client.Range("C" & r).Value = entry.Range("H12").Value
            client.Range("D" & r).Value = entry.Range("H15").Value
            client.Range("E" & r).Value = entry.Range("H18").Value
            Exit For
        End If
        Next r
End Sub

Sub Follow_up()
    Dim lr As Long
    lr = fup.Range("A" & Rows.Count).End(xlUp).Row + 1
    fup.Range("A" & lr).Value = entry.Range("N6").Value
    fup.Range("B" & lr).Value = entry.Range("N9").Value
    fup.Range("C" & lr).Value = entry.Range("N12").Value
End Sub

Copy this macro to the worksheet module of sheet “Entry”

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$H$6" Then
        Range("H9").Value = Application.WorksheetFunction.VLookup(Target.Value, client.Range("A1:E1000"), 2, False)
        Range("H12").Value = Application.WorksheetFunction.VLookup(Target.Value, client.Range("A1:E1000"), 3, False)
        Range("H15").Value = Application.WorksheetFunction.VLookup(Target.Value, client.Range("A1:E1000"), 4, False)
        Range("H18").Value = Application.WorksheetFunction.VLookup(Target.Value, client.Range("A1:E1000"), 5, False)
    End If
End Sub

How does it work?

The “Add_Client” macro identifies the last row in the sheet “Client” and appends the clients’ data from the sheet “Entry”. With the “Update_Client” macro, you can update your Client’s details. The Follow_up macro will help you to track your meetings with your Client. However, if an Excel corruption occurs, dropdowns might not work. In such a case, just remove the corrupted range, add the data validation again.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word corruption and outlook recovery software products. For more information visit www.datanumen.com.