{"id":1478,"date":"2021-03-16T23:16:58","date_gmt":"2021-03-16T15:16:58","guid":{"rendered":"https:\/\/indonesiangems.com\/blog\/?p=1478"},"modified":"2021-03-19T06:22:00","modified_gmt":"2021-03-18T22:22:00","slug":"excel-purchase-sales-data-entry-form","status":"publish","type":"post","link":"https:\/\/indonesiangems.com\/blog\/excel-purchase-sales-data-entry-form\/","title":{"rendered":"Purchase\/Sales Data Entry Form in Excel with ActiveX controls"},"content":{"rendered":"

Last updated on March 19th, 2021 at 06:22 am<\/p>\n\n\n\n\n

\n
switch language to bahasa<\/a><\/div>\n<\/div>\n\n\n\n

The topic in this page is a continuation from the previous page, Pivot Table for Accounts Payable\/Receivable and Inventory<\/a> Now Mr. Adi will make a simple application for Purchase\/Sales Data Entry Form<\/a> in Excel using ActiveX controls<\/a>.<\/p>\n\n\n\n

Create the form in Excel worksheet<\/em><\/h5>\n\n\n\n

First, Mr. Adi create a new sheet and he name the sheet “SellBuy”. This is the fifth sheet, after the sheets TABLE, DATA, CUSU and STOCK. <\/p>\n\n\n\n

While sheet “SellBuy” active:<\/span>
In cell E1 type =TODAY()<\/span>
In cell E7 type =C7*D7<\/span>
In cell E17 type =SUM(E7:E16)<\/span>
Copy the formula in cell E7, then paste it to range E8:E16.<\/p>\n\n\n\n

The raw design of the form is something like the sample image below.<\/p>\n\n\n\n

\"Preparation<\/a>
Raw design of the Sales and Purchase Data Entry Form
(please ignore the foreign name in the image)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n
Add the ActiveX controls<\/em><\/h5>\n\n\n\n

Still in sheet “SellBuy”, within Developer menu tab, click Insert then click Text Box ActiveX control. As seen in the image below.<\/p>\n\n\n\n

\"ActiveX<\/a>
ActiveX controls in Excel menu<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

Put the Text Box on sheet “CUSU” by clicking any cell on the sheet, click the Properties button, then type SearchBox for the (Name). Do the same to add the List Box ActiveX control, and name it ListName for the (Name) in the Properties window. This process is seen in the gif animation below.<\/p>\n\n\n\n

\"Add<\/a>
Adding a Text Box and a List Box ActiveX control<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

Next is adding the ActiveX Command Button control then give it a name and it’s caption. The process is seen in the gif animation below.<\/p>\n\n\n\n

\"Add<\/a>
Adding a Command Button control
(please ignore the foreign name in the image)<\/figcaption><\/figure><\/div>\n\n\n\n

In this case, the form will need six Command Button controls. Below is the table for each control’s name and\/or caption.<\/p>\n\n\n\n

\n \n \n \n \n \n \n \n \n \n \n \n
Control<\/th>\n (Name)<\/th>\n Caption<\/th>\n <\/tr>\n
TextBox<\/td>\n SearchBox<\/td>\n <\/td>\n <\/tr>\n
ListBox<\/td>\n ListName<\/td>\n <\/td>\n <\/tr>\n
CommandButton<\/td>\n CU<\/td>\n CU<\/td>\n <\/tr>\n
CommandButton<\/td>\n SU<\/td>\n SU<\/td>\n <\/tr>\n
CommandButton<\/td>\n Item<\/td>\n Item<\/td>\n <\/tr>\n
CommandButton<\/td>\n Kas_bon<\/td>\n CASH<\/td>\n <\/tr>\n
CommandButton<\/td>\n NewName<\/td>\n NEW<\/td>\n <\/tr>\n
CommandButton<\/td>\n InputToData<\/td>\n Input To Data<\/td>\n <\/tr>\n
<\/td>\n <\/tr>\n <\/table>\n<\/div>\n\n\n\n

Below is the image after Mr. Adi add 1 TextBox, 1 ListBox and 6 CommandButton controls.<\/p>\n\n\n\n

\"\"<\/a>
ActiveX controls on sheet “CUSU”
(please ignore the foreign name in the image)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

It’s OK for the time being the controls’ position are still messy. Mr. Adi will tidy them by using a macro code.<\/p>\n\n\n\n

Summary<\/em><\/h5>\n\n\n\n

Below is the summary table of each control function.<\/p>\n\n\n\n

\n
Control’s Name<\/th>Function<\/th><\/tr>
CU<\/td>Sales Transaction – Get the Customer list name<\/td><\/tr>
SU<\/td>Purchase Transaction – Get the Supplier list name<\/td><\/tr>
Item<\/td>Get the Item list name<\/td><\/tr>
CASH<\/td>Payment Cash \/ non-Cash<\/td><\/tr>
SearchBox<\/td>type to search a name<\/td><\/tr>
ListName<\/td>List the name according to what is being typed<\/td><\/tr>
NewName<\/td>when there is a new Customer\/Supplier\/Item <\/td><\/tr>
InputToData<\/td>Purchase or Sales Data Entry to the Main Data<\/td><\/tr><\/table>\n<\/div>\n\n\n\n

Until here, Mr. Adi has finished a half way in making the Purchase\/Sales Data Entry Form in Excel. In the next page, he will make the macro codes for each controls.<\/p>\n\n\n\n

If there is something which is not clear regarding this topic, please don\u2019t hesitate to ask in the Comment box. I\u2019ll try my best to answer.<\/p>\n\n\n\n