Excel實戰技巧63： 製作具有數據導航功能的用戶窗體

• 2019 年 12 月 4 日
• 筆記

本文講述如何連接用戶窗體與ADO記錄集，最終創建一個與Access窗體相似的用戶窗體，可以導航至前一條記錄、下一條記錄、第一條記錄、最後一條記錄，等等。

設計用戶窗體

在VBE中，插入一個用戶窗體，在其中添加文本框、按鈕、標籤等控制項，如下圖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:UsersAdministratorDocuments"      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所示。

注意，如果你運行這個用戶窗體出現錯誤，檢查一個是否在VBE中設置了對Microsoft ActiveXData Objects 2.X Library的引用；或者是否Initialize事件中對資料庫的引用路徑和名稱有誤。

有興趣研究本示例的朋友，可以在完美Excel公眾號的底部發送消息：

導航記錄集

下載示例工作簿。