Excel Templates

Excel Template Form DataBase with Excel and VBA

Have you seen the new VBA FormBuilder 2018? Check it out!

The Excel Template Form DataBase with Excel and VBA intends to be a base for any kind of application which going to work with database and forms interactions, but, using Microsoft Excel and plain VBA.

This template contains 2 VBA UserForms. The main one is the Navigation Form, where you can navigate among the records in the database and also, Create, Edit and Delete them. As you can see in the images below, the navigation is presented by indexing and counting of records.

Figura 1 – Visual da tela de cadastro
Picture 1 – Navigation UserForm

The second one is the UserForm for search and filter, where you can create a huge amount of search combinations and sort options. The special in here is, instead of travel through a wide array of records, I decided to use ADO (Active Data Objects) and SQL (Structured Query Language) to make the search options easier and powerful. If you know a bit of SQL, you know what I mean. But if you don’t, well, what are you waiting for?

Figura 2 - Visual da tela de pesquisa
Picture 2 – Userform for filter and sort
Figura 3 – Visual da tela de pesquisa – Versão 2
Picture 3 – Userform for filter and sort – Version 2

I’ve added the auto-numeric ID functionality here, so, each record contains an ID which is unique and controlled by the code, as you can see in the Relational DataBase Systems, like Microsoft Access. The first column of the table/worksheet is automatically considered as the ID column.

In this version, the Excel Template Form DataBase with Excel and VBA doesn’t support strong type consistency and required fields. I hope to add these functionalities in the future.

Also, the Userform for filter and sort depends on the Navigation UserForm due to, when you double-click on a record in the Listbox’s result in Userform for filter and sort, the code automatically navigates to respective record in the Navigation UserForm.

Version 1

http://www.tomasvasquez.com.br/downloads/modelocadastro.zip

Version 2

http://www.tomasvasquez.com.br/downloads/ModeloCadastro_v2.zip

Version 2 Change Log – Userform for filter and sort

  • txtCidade field removed
  • ListBoxCidades added, which allowing filter the results using multiple values selected in this control
  • MontaClausulaWhere function changed to support the functionality mentioned above
  • Export results to a new Workbook function added

Version 3

http://www.tomasvasquez.com.br/downloads/ModeloCadastro_v3.zip

Version 3 Change Log – Split forms and database files

In this version, I divided the application in two files. One of them contains all code, forms and logic. The other one, of course, contains the data, and only the data. It’s very important to mention it, because the data file is the one which you will need to save and protect with all of your heart.

The Excel does not allow the VBA code handles the file in a multi-user way. So, I did my best in the code, trying to let the data file opened only when it’s required, closing it after each interaction. But, there is no concurrency consistency. So, if you have many users, you have to consider this.

Naturally, you can improve this code share it, like I’m doing here. 🙂

Now, there is a worksheet which is used as the application setup, where you can set path and the data filename.

So, the result are:

  • ModeloCadastro_FrontEnd.xls – Contains the application file, with VBA code and UserForms
  • ModeloCadastro_Dados.xls – Contains the data

Version 3.5

http://www.tomasvasquez.com.br/downloads/ModeloCadastrov3ListView.zip

Version 3.5 Change Log – Then new UserForm filter with ListView control

This is a huge collaboration from the Forum’s user, Mauro Coutinho, who has created a different version of the UserForm filter and sort using the ListView control instead of ListBox. The ListView is as more complete and powerful control, bringing more features in the result set navigation.

Tela de Pesquisa com ListView
Picture 4 – UserForm filter and sort using ListView control

If you find any bugs on this template, please, send a message to us or visit our forum!

Enjoy!

Tecnologia e Programação

%d bloggers like this: