Home Office LTD company. Database

Authors Avatar

Tarun Nayyar – Implementation

Implementation

In my workbook for the Home Office LTD company, I have designed seven main sheets:

  1. Main menu
  2. Customer database
  3. Product information
  4. Generic order form
  5. Discount table
  6. Monthly review
  7. Yearly review

Other sheets can be made from these seven base sheets. This is done by edited the information on the relevant sheet, and then “save as” another name to keep both sheets i.e. monthly review… once January is completed, that sheet is saved as “Jan stats”, then the info is deleted off the sheet then figures are entered for February then that is saved as “Feb stats”. The same can be done for the generic order form and yearly review.

Main menu sheet

The purpose of this sheet is simply that is it a directory for the user. From any other sheet in the workbook, with a click of a button (which is on every sheet) the user can go back to the main menu and chose from the buttons which sheet they want to go to. With another click of a button, the user is then taken to the sheet that they selected on the main menu sheet. The purpose of this is so that there is little confusion as possible for the user, as when looking through so many figures, stats and information, the user could easily get confused with what sheet they want an where it is. It works by using six macros to get to other sheets and another six to get back to the main menu.

Buttons that go to

the sheet as stated on the button

In the screen shot above, it shows the worksheet working perfectly. With this sheet, there were no problems at all creating it and the macros involved. In this sheet, there are five macros. These were made by pressing the record macro button, while starting on this page, while recording I clicked on to another sheet and then stopped recording the macro. After the macro had finished recording, I created a button, which I assigned the macro to and renamed the button after the sheet that it clicked on i.e. the monthly review sheet.

This macro takes the user back to the main menu from any sheet.

This is how it is shown is Visual Basic.

This screenshot shows in visual basic the macro that takes the user to the customer database from the main menu sheet.

.

This screenshot shows the macro that takes the user from the main menu to the discount table

Screenshot of the macro opened in visual basic that takes the user from the main menu to the monthly review.

                

This is a screenshot of the Visual Basic macro, which takes the user from the main menu to the order form sheet.

This screenshot shows the macro that takes the user to the yearly review sheet from the main menu.

This screenshot shows the macro that transfers the user between the monthly review and yearly review.

 

The macro being assigned to the button along with the recording and playback macro buttons in the top left hand corner

Customer database sheet

The purpose of this sheet is so that the user can keep a record of all the customers that have purchased products from the firm. It holds all their relevant contact details such as names, address, contact number, email and the amount paid by the client and date on which the products were bought. This sheet can also be used to create a mail merge file in word; this can be used for sending out mass newsletters and information to all clients with their own personal details on the letter.

Also another purpose of this sheet is so that the user can add the amount paid and date of custom to the list so that it can be reviewed and analysed in the monthly and yearly review sheets. All of which can either be manually typed in by the user or by copying and pasting the information into the relevant sheets.

This screenshot above shows how using a macro will not allow the user to enter a new client on to the database. One way of resolving this issue is that the user can type the data in the spreadsheet under the column headings and under the other customers. Another way is for the user to use vlookup, but a problem with this is that it only works for one entry and when you want another entered, it did not work. It just changed the previous clients’ information that was entered using vlookup. The solution is to insert a new line above the new entry. This allows there to be a space in the sheet for the next new client. This is because when creating the macro the information was pasted into row 14, and with the inserted line above the data transferred, it allowed the future data to be pasted into row 14 again. This will work every time when entering transferring the data from the invoice sheet.

Join now!

The screenshot shows the macro used to add new customers straight to the database. With a click of a button, all the relevant information is automatically transferred. This is done by recording a macro then copying and pasting the information to the relevant cells on the correspondent sheet, then inserting a new line for the next entry. The macro is then stopped and with the “send to database button” the information circled in a red box, is transferred to the customer database. The purpose of this is so the user does ...

This is a preview of the whole essay