excel图表制作：制作具有数据导航功能的用户窗体

本文述如何连接用户窗体与ADO记录集，最终创建一个与Access窗体相似的用户窗体，可以导航至前一条记录、下一条记录、第一条记录、最后一条记录，等等。

设计用户窗体

在VBE中，插入一个用户窗体，在其中添加文本框、按钮、标签等控件，如下图1所示。

图1

这个用户窗体中的控件及相应的名称如下：

• 用户窗体名称：ufEmployee

• 用户窗体标题：员工记录

• 在用户窗体中，从上至下，从左至右，创建下面的控件：

• 员工ID文本框名称：tbxEEID

• Tag：Field0

• 姓氏文本框名称：tbxLastName

• Tag：Field1

• 名字文本框名称：tbxFirstName

• Tag：Field2

• 出生日期文本框名称：tbxBirthDate

• Tag：Field3

• 雇用日期文本框名称：tbxHireDate

• Tag：Field4

• 命令按钮名称：cmdFirst

• Caption： <<

• Tag：ButtonFirst

• 命令按钮名称：cmdPrev

• Caption： <

• Tag：ButtonPrev

• 命令按钮名称：cmdNext

• Caption： >

• Tag：ButtonNext

• 命令按钮名称：cmdLast

• Caption：

• Tag：ButtonLast

编写代码

下面，编写代码来管理这个用户窗体，即处理数据和用户窗体中的控件。

这个用户窗体需要6个事件：Initialize（当用户窗体打开时）、QueryClose（当用户窗体关闭时），以及每个命令按钮的单击事件。

需要使用事件代码将记录集的当前记录显示在文本框中、以及阻止用户错误操作，例如当处于第一条记录时单击命令按钮cmdPrev（<）。

在用户窗体模块中，声明两个模块级的变量：

• Dim mADOCon As ADODB.Connection

• Dim mADORs As ADODB.Recordset

使用模块级变量的原因是：当用户窗体开启时，Connection和Recordset对象在作用域内。

下面的过程使用记录集中当前记录的数据填充文本框。

• Private Sub FillTextBoxes()

• Dim cTxtBx As Control

• Dim lFldNo As Long

• For Each cTxtBx In Me.Controls

• If cTxtBx.Tag Like “Field*”Then

• lFldNo = Mid(cTxtBx.Tag, 6)

• cTxtBx.Text = mADORs.Fields(lFldNo)

• End If

• Next cTxtBx

• End Sub

记得前面在设计用户窗体时给文本框设置的Tag属性吗？接下来看看我们为什么设置这个属性。

上面的程序代码遍历用户窗体中所有的控件，如果控件具有像Field0、Field1、Field2等形式的标签（tag），就从记录集中获取与标签相同名称字段的数据来填充相应的文本框。当打开用户窗体或者单击任一按钮时，需要改变文本框来响应事件。因此，Initialize事件和四个按钮中任一按钮的单击事件都将调用上面的程序。在调用上面的程序之前，这些事件将首先设置当前记录。

上述程序代码的一个好处是，你可以添加和删除文本框，而无须修改代码。前提是只要正确填写了Tag属性，就会正确填充文本框。然而，不会检查来确保字段实际存在。例如，如果设置一个Tag为Field10，但记录集仅有9个字段，那就会弄混淆。在示例代码中，为了简单起见，我们不会创建这样明确的错误检查代码。

下面的程序代码控制命令按钮（CommandButtons）。假如已经到达第一条记录，我们不想提供给用户到达第一条记录或者前一条记录的按钮选项，因为已经是第一条记录，没有更前一条记录了。同样地，当前记录是最后一条记录时，禁用最后一条记录和下一条记录按钮。每次触发一个事件时，都要确保按钮响应当前的状态。

• Private Sub DisableButtons(ParamArray aBtnTags() As Variant)

• Dim i As Long

• Dim ctl As Control

• For Each ctl In Me.Controls

• ctl.Enabled = True

• For i = LBound(aBtnTags) To UBound(aBtnTags)

• If ctl.Tag = aBtnTags(i) Then

• ctl.Enabled = False

• Exit For

• End If

• Next i

• Next ctl

• End Sub

对于上面的程序，使用了ParamArray参数，可以发送任意数量想要禁用的按钮的名称。例如，如果想禁用第一个和前一个按钮，就可以像下面这样调用该程序：

DisableButtons “ButtonFirst”, “ButtonPrev”

除了传递给参数的按钮外，每个按钮都可用，即能被用户单击。如果不带任何参数调用该程序，那么所有的按钮都是可用的。该程序遍历用户窗体中的所有按钮，将其Tag属性与参数列表对比，如果发现匹配则禁用该控件。该程序不会区分文本框、命令按钮或任何其它类型的控件。你只需确保使用了正确的Tag属性，以免禁用了你不想禁用的控件。

现在，创建记录集，确保使用有用的信息来填充文本框。

无论用户窗体在什么时候打开都会触发Initialize事件，此时适合创建记录集。这样，无论何时用户窗体显示，我们都知道有可用的记录集存在。

• Private Sub UserForm_Initialize()

• DimsConn As String

• Dim sSQL As String

• Dim sDbPath As String

• Dim sDbName As String

• ‘存储数据库的路径和名称

• ‘可以修改为你自已的路径和名称

• sDbPath =”C:\Users\Administrator\Documents\”

• sDbName = “Northwind”

• ‘存储连接字符串

• sConn = “DSN=MS Access Database;”

• sConn = sConn & “DBQ=” &sDbPath & “” & sDbName & “.mdb;”

• sConn = sConn & “DefaultDir=”& sDbPath & “;”

• sConn = sConn &”DriverId=281;FIL=MS Access;MaxBuffersize=2048;PageTimeout=5;”

• ‘存储SQL语句

• sSQL = “SELECT 雇员.雇员ID,雇员.姓氏,”

• sSQL = sSQL & “雇员.名字,雇员.出生日期,雇员.雇用日期 “

• sSQL = sSQL & “FROM ” &sDbPath & “” & sDbName & “.mdb” & “.雇员” ‘此处要注意如果路径中有空格则需添加单引号来引用

• ‘创建新的连接和记录集

• Set mADOCon = New ADODB.Connection

• Set mADORs = New ADODB.Recordset

• mADORs.CursorLocation = adUseClient

• ‘打开连接和记录集

• mADOCon.Open sConn

• mADORs.Open sSQL, mADOCon, adOpenDynamic

• ‘到第一条记录

• mADORs.MoveFirst

• ‘调用特定目的的过程

• FillTextBoxes

• DisableButtons “ButtonFirst”,”ButtonPrev”

• End Sub

这个程序的大部分代码是设置字符串，那些烦人的连接字符串和SQL语句是难以编写的。其中一种方法是在Excel中创建外部数据表（数据>获取外部数据），然后读取Connection和CommandText属性。接着，做一些修改，例如从连接字符串中移除ODBC以及使用变量代替数据库信息。一旦建立了外部数据表，可在立即窗口输入：

• Print Sheet1.QueryTables(1).Connection

• Print Sheet1.QueryTables(1).CommandText

将获得两个长字符串，可以将其复制并粘贴到代码中。也可以像上面程序中一样，将代码分成几个字符串。

当关闭用户窗体时，将触发QueryClose事件。如果用户窗体不打开，就不需要记录集，因此在该事件中要将其释放并清空内存。

• Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

• mADORs.Close

• mADOCon.Close

• Set mADORs = Nothing

• Set mADOCon = Nothing

• End Sub

下面，介绍如何编写命令按钮代码，使记录来回移动。

基本上有两类命令按钮，一类按钮到达记录集的两端即第一条记录和最后一条记录，一类按钮一次移动一条记录。所有的四个命令按钮都做三件事：使正确的记录为当前记录、调用FillTextBoxes过程和调用DisableButtons过程。“移动一条记录”按钮有一个额外的步骤，即决定哪条记录是当前记录。如果是第一条或最后一条记录，需要禁用不同的命令按钮。

• Private Sub cmdFirst_Click()

• mADORs.MoveFirst

• FillTextBoxes

• DisableButtons “ButtonFirst”,”ButtonPrev”

• End Sub

• Private Sub cmdLast_Click()

• mADORs.MoveLast

• FillTextBoxes

• DisableButtons “ButtonLast”,”ButtonNext”

• End Sub

• Private Sub cmdNext_Click()

• mADORs.MoveNext

• FillTextBoxes

• If mADORs.AbsolutePosition =mADORs.RecordCount Then

• DisableButtons “ButtonLast”,”ButtonNext”

• Else

• DisableButtons

• End If

• End Sub

• Private Sub cmdPrev_Click()

• mADORs.MovePrevious

• FillTextBoxes

• If mADORs.AbsolutePosition = 1 Then

• DisableButtons “buttonFirst”,”ButtonPrev”

• Else

• DisableButtons

• End If

• End Sub

上述程序代码中， MoveNext方法、MoveFirst方法、MovePrevious方法和MoveLast方法都是Recordset对象的方法。

最终的效果如下图2所示。

图2

注意，如果你运行这个用户窗体出现错误，检查一个是否在VBE中设置了对Microsoft ActiveXData Objects 2.X Library的引用；或者是否Initialize事件中对数据库的引用路径和名称有误。