10.2. 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(  ) Unload Me End Sub

The last piece of this is to update the original code to take the parameters from the form instead of taking them from the worksheet. This is shown in Example 10-7.

Example 10-7. Chapter 7's code modified to use a user form

 Public Sub OpenSQLWriteExcel(usn As String, pwd As String) Dim adocn As ADODB.Connection Dim adoconnrs As ADODB.Recordset Dim adors As ADODB.Recordset Dim adofld As ADODB.Field Dim ConnString As String Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim xlrng As Excel.Range Dim x As Integer Dim UserName As String Dim Password As String UserName = usn Password = pwd Set adoconnrs = New ADODB.Recordset adoconnrs.Open "C:\Documents and Settings\All Users\" & _                "Documents\SQLConn.XML" adoconnrs.MoveFirst While Not adoconnrs.EOF   ConnString = ConnString & _       adoconnrs.Fields(0).Value & " = '" & adoconnrs.Fields(1).Value & "';"   adoconnrs.MoveNext Wend adoconnrs.Close ConnString = ConnString & "User ID = '" & UserName & "';" ConnString = ConnString & "Password = '" & Password & "';" Set adocn = New ADODB.Connection adocn.ConnectionString = ConnString adocn.Open Set adors = New ADODB.Recordset adors.Open "pubs.dbo.Authors", adocn adors.MoveFirst Set xlwb = ActiveWorkbook Set xlws = xlwb.Worksheets.Add xlws.Name = "Data" x = 1 For Each adofld In adors.Fields   xlws.Cells(1, x).Value = adofld.Name   x = x + 1 Next adofld Set xlrng = xlws.Range("A2") xlrng.CopyFromRecordset adors xlws.Columns.AutoFit adors.Close adocn.Close Set xlrng = Nothing Set xlws = Nothing Set xlwb = Nothing Set adoconnrs = Nothing Set adocn = Nothing Set adors = Nothing End Sub

The only changes made to this code were to use the variables usn and pwd to pass the username and password instead of pulling the information from the worksheet. If you look back to Chapter 7, we used the following lines of code to pull in the username and password:

     UserName = Sheets("Connection Info").Range("B1").Value     Password = Sheets("Connection Info").Range("B2").Value

In this example, we needed to refer to the variables we created. You could also have simply removed the variable declaration for UserName and Password and just used UserName and Password in place of usn and pwd. Either way works, and I wanted to use as much of the original code as possible.

While it might not matter to you whether you use a user form or cells on a worksheet, using a user form certainly does make your mini-application look better. In addition, you are not limited to having things set up in rows and columns when you use a user form, and your ability to force data validation is much better when using a user form.