Ali's Photography Space…

In this short project we will learn how to create a simple CRM system and and will build forms to collect needed data and store it in tables.

Case Study:
We want to create a simple CRM system to collect data about our employees such as [Name, Department, Salary], we will keep the system (Forms and codes as simple as we can). In this part, we will work on fomrs and code to collect the Employee data.

First, we will open an Excel file, and will create Three Tabs (Sheets), and will name them [Form, Data, Table]. As in figure 1

As in figure 1

Now try to create the formes for Employee and another for Departments (Download the Excel file here).

Then in the Data Sheet starting from A1 create a table as A1: Name, B1: Department, C1: Salary, and in the F1 we will create a table named Department List. As in figure 2

figure 2

and enter one row of data as in figure 2. Then we also need to create a Dynamic Name Range, for both employee name and Department List, to do so we:
1. Go to Data Sheet.
2. Select the Employee table that we create.
3. in the “NameBox” just give the table a name as “emp”.

Do the same to the Department List table dep_list, we will use these names later.

Coding: Now we will write the VBA code to make a list of Departments we have. To do so we will select the button of SAVE under the Department Form, Click the Right mouse button, and select Assign Macro, then select New and OK, after that the VBA application will start. We will write this code:

Sub save_new_dep()

End Sub

figuer 4

In this line
next_row = Sheets(“Data”).Range(“F” & Rows.Count).End(xlUp).Offset(1).Row we will get the next empty row in the department list.

in this line Sheets(“data”).Range(“F” & next_row).Value = Sheets(“form”).Range(“J7”) we will copy the date we just enter as a New Department to the Data Sheet Depatrtment List. Then with Sheets(“form”).Range(“J7”).ClearContents we will clear the content.

So now we can enter some department names: IT, Fin, Economy

We will do the same thing for the Employee Form, so with an open VBA application write the following code. as in figure 5

Sub save_new_emp()

figure 5

The first line of the code is to get the next empty line in the Employee table, then we will use it to copy the data from the form to the data table. In my form, the Name is in D7, the Department in D8, and the Salary in D9. here is the code line for the name: Sheets(“data”).Range(“A” & next_row).Value = Sheets(“form”).Range(“D7”), then we clear the form as we did with the Department form. In the Employee form (Range”D9″) we need to make it as a DropDown list of all Departments we have so we can select from the list, to do that follow the coming steps ..
1. Select Cell D9.
2. From the Menu Select Data then Data Validation, a popup box will apear as in figure 6, do as shown, here we will use the Dynamic Name range we create “dep_list” as a source. figure 6

figure 6

At this point we have two Forms, one for the Department List and one for the Employee (Name, Department, Salary), both forms has SAVE buttons, the Data will be saved in the Data Sheet

To Download Excel files Click-Here