Excel Login Form VBA to Connect to PHP (Part 1)

Excel Login Form

Excel Login Form

I want to connect my PHP applications to Microsoft Excel and allow Excel to pull and push data into MySQL tables.  The first step on the journey is to create an Excel login form.

Creating the Excel Login Form

Visual Basic IconFrom a new Blank workbook, select “Developer” from the ribbon tab and then click the “Visual Basic” icon.

From the Visual Basic window, insert a new form and name it LoginForm.  Add two fields and name them “UserID” and “Password”.  Finally add a “Connect” button to the form.

When you are finished, you should have a form that looks something like the above picture.  See the Excel Easy post “Userform” if you need help. http://www.excel-easy.com/vba/userform.html

Saving the User ID to the Registry

Having to type the user id over and over is a waste of time.  Saving the user id into your local registry and setting the focus to the password field is simple way to to provide automation for the user.  We can do this by add form initialization code into the visual basic for the application.  By right clicking on the form from the lefthand side “Project” tree you can view the code for the form.

Add the following to save the user id to the registry.

Adding the login icon the the Quick Access Toolbar

Now we need a way to trigger the login form.  By putting a icon in the Quick Access Toolbar we can give the user quick access to the login form.

First we will need a entry point for our form.  From the Visual Basic window select “Insert” then Module.  This should insert a new module and open a code window.

Assuming you named your form “LoginForm” add the following code to you module.

This is the subroutine that will be called when we click the quick connect icon.

Next, right click on the title bar on the save icon and select “Customize Quick Access Toolbar…”.

Quick Access Toolbar Menu

 

From the Options menu, change the Commands to “Macros”, change the “Customize Quick Access Toolbar” to “For (You file name)”, select “Connect_Click” from the lefthand side and click the “Add > >” Button.  Finally click “OK” to save your options.

Quick Access Toolbar Options

Conclusion

You should now see your icon on the Quick Access Toolbar.

Custom Quick Access Toolbar

By clicking your new icon you should see your login form.

VBA Login Form

VBA Login Form

Clicking the Connect button will save your User ID into the registry, closing the login form and reopening will show your the last User ID entered with the Password field selected.

In Part 2 we will connect to a PHP application using VBA-Web library https://github.com/VBA-tools/VBA-Web.

References:

Creating an Excel User form.

Saving setting to the Registry.

Getting setting from the Registry.

Leave a Reply