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:
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.
Leave a Reply