1. Home
  2. Docs
  3. Getting Started
  4. Getting Started: Start Main Menu with Excel Startup

Getting Started: Start Main Menu with Excel Startup

In this article I will explain to you how you can recreate the example that you can download here:

 

Or, of course, you can use this, or any of the downloaded examples and build on top of it your own application by adding few tables to existing database, scan them with the Virtual Form Designer, adding this Virtual Forms to the menu and your application is ready!

 

We will use the Microsoft Excel & VBA for the Frontend, and the Microsoft Access database for the Backend (as the data source).

In this first few articles, we will only focus on different scenarios how we can open the Main Menu or a Virtual Form from within VBA. In later articles, we will focus on how to work with Main Menu, how to create Virtual Forms with Virtual Form Designer and how to respond to events that Virtual Form Control fires and how to interact with controls that are on the Virtual Forms (for example TextBoxes, Buttons, labels, grid control,…)

In this example when we open this Excel Workbook the Virtual Form Control will connect to the Microsoft Access database that is in the same folder as this Excel Workbook.

Then the Excel is getting minimized and the Virtual Form Control displays the Main Menu.

And when we close the Main Menu the Virtual Form Control triggers the SafeToQuit event where we close this Excel Workbook.

 

To get started from scratch you first need to install the Virtual Forms Framework and prepare the VFFile where the Virtual Forms stores the definitions data. You can learn more details on how to do this by following this article.

Step 1

Save your Excel Workbook as .xlsm

Now open your Microsoft Excel and save the Workbook as the “macro-enabled workbook format (.xlsm)” in the same folder where you have your VFFile.

Note:

It is not necessary to save the Excel Workbook in the same folder where you have your VFFile, but for better organization and easier following of this tutorial, we will save our Excel Workbook to the same folder.

 

Step 2

Add a reference to the Virtual Form control in VBA

  1. In Microsoft Excel go to the Developer tab and click on the Visual Basic

 

Excel Developer tab VBA

 

2. In Visual Basic for Applications (VBA) click on Tools > References

 

Excel VBA Tools References

 

3. In the references list in VBA search for the ‘VirtualForm 2.0’  tick the checkbox and click OK.

 

Excel VBA References VirtualForm 2.0

 

Step 3

Insert the code to the Workbook Module in VBA editor

Double click on the Workbook Module in VBA editor

Excel VBA Workbook Module

 

Add this code to the Workbook Module in VBA

 


 

Let me explain this VBA code line by line


This line of code is placed in the declaration part of the Workbook Module.

Public, because we want this VF1 object to be publicly accessible from anywhere.

WithEvents, this exposes the events that Virtual Form Control fires so that we can consume, respond and use these events (for example: Before Form Open, Before Save, After Save, On Textbox Validate, On Button Click,…).

VF1 is the name that we want that out instance of the Virtual Forms control uses (you can name it whatever you want, but it must start with a letter and can’t contain spaces).

VirtualForm2.VirtualForm is the type of the object that we want that the VF1 object will be (this is why we added the reference in the previous step to VirtualForm2.0)

 


Workbook_Open is the event that is triggered when the Excel workbook opens.


In this line of code, we are creating a new instance of the VF1 object.


Telling the VF1 (the virtual form control instance) that we will use the Access database as the data source.

We could also use other types of data sources (0 = Access, 1 = MSSQL, 2 = MySQL, 3 = Excel).


Telling the virtual form instance where the definition file is located.


Providing the connection string for the Access database with the location of the Access database. Every data source (database) uses a different connection string.


Here we are minimizing the Microsoft Excel & VBA Editor window so that the Main Menu will be at the top and visible to our user.


Now we are telling the Virtual Form Control to display the Main Menu.


Workbook_BeforeClose is the event that is triggered right before the Excel workbook closes.


With this line of code, we are telling to VBA that if an error is raised, but only within this subroutine (Local), we want that it skips and go to the next line of VBA code.

 


Before the Excel Workbook closes, with this Quit method, we are telling the Virtual Form Control to run some internal functions that will close all opened Virtual Forms and to disconnect the connection to the database.

 


SafeToQuit is an event that Virtual Form Control fires when all the Virtual Forms and the Main Menu (if we have opened it) is closed, and that if we want we can close the parent application (in this case the Excel).


This line of code closes the Microsoft Excel Workbook.

 

Was this article helpful to you? Yes No

How can we help?