Integrating Excel and Access

Accepting Parameters

If you look back to Chapter 7, where we had an Excel workbook take the username and password from two cells in the worksheet to connect to the database, this is a good place to use a user form instead. Open the workbook and use Alt + F11 to go to the Visual Basic Editor. Right-click on the name of the project and go to Insert → UserForm. Now you have a blank user form. You need two labels, two textboxes, and two buttons, and it could look similar to Figure 10-3.

There are a couple items that make this form a little more useful. First, in the properties of the password box, you can choose a character to display so that someone cannot see what is being typed. Generally, I use the "*" character to do that. However, you can use any character that you want. The code then changes slightly from the original. First, the code on the worksheet's button needs to change to what you see in Example 10-4.

Example 10-4. Worksheet button code

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

This code shows the form created in Figure 10-3. The codes for the Connect and Cancel buttons are shown in Example 10-5 and Example 10-6.

Figure 10-3. Example of a database login form

Example 10-5. Connect button code

Private Sub CommandButton1_Click()
Call Module1.OpenSQLWriteExcel(Me.TextBox1.Value, Me.TextBox2.Value)
End Sub

Example 10-6. Cancel button code

Private Sub CommandButton2_Click() ...