在應用程式開發中,經常需要處理大量數據輸入。若能在程式中提供類似電子表格的直觀輸入界面,將大幅提升使用者體驗與效率。雖然 VB 內建的 Grid 控制項僅具備顯示功能,但透過適當的改造,即可將其轉化為可編輯的試算表式介面。本文將詳細說明如何在 VB 5.0 環境下,結合 TextBox、Grid、ScrollBar 等控制項,打造一個功能完整的類試算表數據錄入元件。
核心概念是利用一個隱藏的 TextBox 作為實際編輯器,當使用者點選 Grid 某儲存格時,將 TextBox 移動到對應位置並顯示該儲存格內容,使用者編輯完成後再寫回 Grid。這樣既保留了 Grid 的表格顯示優勢,又獲得了文字方塊的編輯能力。
Dim inputColNum As Integer, inputRowNum As Integer
在表單載入時,設定 Grid 的儲存格寬高、總行列數、可視範圍,並根據解析度計算控制項尺寸與位置,同步捲軸範圍。
Private Sub Form_Load()
Grid1.ColWidth(0) = 540
Grid1.RowHeight(0) = 216
inputColNum = 13
inputRowNum = 15
Grid1.Cols = 20
Grid1.Rows = 20
' 依解析度微調(800×600 為 12,640×480 為 15)
Const twipsPerPixel = 12
Grid1.Height = (inputRowNum + 1) * (Grid1.RowHeight(0) + Grid1.GridLineWidth * twipsPerPixel) + HScroll1.Height
Grid1.Width = (inputColNum + 1) * (Grid1.ColWidth(0) + Grid1.GridLineWidth * twipsPerPixel) + VScroll1.Width
HScroll1.Width = Grid1.Width - VScroll1.Width
VScroll1.Height = Grid1.Height - HScroll1.Height
HScroll1.Left = Grid1.Left
HScroll1.Top = Grid1.Top + Grid1.Height - HScroll1.Height
VScroll1.Left = Grid1.Left + Grid1.Width - VScroll1.Width
VScroll1.Top = Grid1.Top
HScroll1.Min = 1
HScroll1.Max = Grid1.Cols - inputColNum
VScroll1.Min = 1
VScroll1.Max = Grid1.Rows - inputRowNum
' TextBox 初始大小與位置對齊第一個資料儲存格
Text1.Width = Grid1.ColWidth(0)
Text1.Height = Grid1.RowHeight(0)
Text1.Left = Grid1.Left + Grid1.ColWidth(0) + Grid1.GridLineWidth * twipsPerPixel
Text1.Top = Grid1.Top + Grid1.RowHeight(0) + Grid1.GridLineWidth * twipsPerPixel
Text1.Visible = True
' 初始化標題列與行號
For i = 1 To Grid1.Cols - 1
Grid1.Col = i
Grid1.Row = 0
Grid1.Text = Str(i)
Grid1.Col = 0
Grid1.Row = i
Grid1.Text = Str(i)
Next i
Grid1.Col = 1
Grid1.Row = 1
Grid1.SelStartCol = 1
Grid1.SelStartRow = 1
Grid1.LeftCol = 1
Grid1.TopRow = 1
End Sub
當使用者在 Grid 上按下滑鼠,取得目前選取儲存格的行列,將內容填入 TextBox,並計算 TextBox 應顯示的座標。
Private Sub Grid1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Grid1.Col = Grid1.SelStartCol
Grid1.Row = Grid1.SelStartRow
Text1.Text = Grid1.Text
Dim colNum As Integer, rowNum As Integer
colNum = Grid1.Col - Grid1.LeftCol + 1
rowNum = Grid1.Row - Grid1.TopRow + 1
Text1.Left = Grid1.Left + (Grid1.ColWidth(0) + Grid1.GridLineWidth * 12) * colNum
Text1.Top = Grid1.Top + (Grid1.RowHeight(0) + Grid1.GridLineWidth * 12) * rowNum
End Sub
Private Sub Grid1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Text1.SetFocus
End Sub
水平與垂直捲軸變更時,更新 Grid 的 LeftCol / TopRow,並同步調整選取儲存格與 TextBox 內容。
Private Sub HScroll1_Change()
Dim startCol As Integer
startCol = Grid1.LeftCol
Grid1.LeftCol = HScroll1.Value
Grid1.SelStartCol = Grid1.SelStartCol + Grid1.LeftCol - startCol
Grid1.Col = Grid1.SelStartCol
Text1.Text = Grid1.Text
End Sub
Private Sub VScroll1_Change()
Dim startRow As Integer
startRow = Grid1.TopRow
Grid1.TopRow = VScroll1.Value
Grid1.SelStartRow = Grid1.SelStartRow + Grid1.TopRow - startRow
Grid1.Row = Grid1.SelStartRow
Text1.Text = Grid1.Text
End Sub
Private Sub Text1_Change()
Grid1.Text = Text1.Text
End Sub
處理方向鍵移動儲存格,並自動捲動捲軸;Enter/Tab 移至下一儲存格;Esc 取消編輯。
Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case vbKeyUp ' 向上
Grid1.Text = Text1.Text
If Grid1.SelStartRow < 2 Then
If Grid1.TopRow > 1 Then VScroll1.Value = VScroll1.Value - 1
Else
If Grid1.TopRow = Grid1.SelStartRow Then
VScroll1.Value = VScroll1.Value - 1
Else
Grid1.SelStartRow = Grid1.SelStartRow - 1
End If
End If
Call UpdateTextBoxPosition
Case vbKeyDown ' 向下
Grid1.Text = Text1.Text
If Grid1.SelStartRow > inputRowNum - 1 Then
If Grid1.TopRow < Grid1.Rows - inputRowNum Then
VScroll1.Value = VScroll1.Value + 1
Else
Grid1.SelStartRow = Grid1.Rows - 1
End If
Else
If Grid1.TopRow = Grid1.SelStartRow - inputRowNum Then
VScroll1.Value = VScroll1.Value + 1
Else
Grid1.SelStartRow = Grid1.SelStartRow + 1
End If
End If
Call UpdateTextBoxPosition
Case vbKeyLeft ' 向左
Grid1.Text = Text1.Text
If Grid1.SelStartCol > 1 Then
Grid1.SelStartCol = Grid1.SelStartCol - 1
ElseIf Grid1.LeftCol > 1 Then
HScroll1.Value = HScroll1.Value - 1
End If
Call UpdateTextBoxPosition
Case vbKeyRight ' 向右
Grid1.Text = Text1.Text
If Grid1.SelStartCol < inputColNum Then
Grid1.SelStartCol = Grid1.SelStartCol + 1
ElseIf Grid1.LeftCol < Grid1.Cols - inputColNum Then
HScroll1.Value = HScroll1.Value + 1
End If
Call UpdateTextBoxPosition
Case vbKeyReturn, vbKeyTab ' 移至下一儲存格
Grid1.Text = Text1.Text
If Grid1.SelStartCol < inputColNum Then
Grid1.SelStartCol = Grid1.SelStartCol + 1
ElseIf Grid1.SelStartRow < inputRowNum Then
Grid1.SelStartCol = 1
Grid1.SelStartRow = Grid1.SelStartRow + 1
End If
Call UpdateTextBoxPosition
End Select
End Sub
Private Sub UpdateTextBoxPosition()
Grid1.Col = Grid1.SelStartCol
Grid1.Row = Grid1.SelStartRow
Dim colNum As Integer, rowNum As Integer
colNum = Grid1.Col - Grid1.LeftCol + 1
rowNum = Grid1.Row - Grid1.TopRow + 1
Text1.Text = Grid1.Text
Text1.Left = Grid1.Left + (Grid1.ColWidth(0) + Grid1.GridLineWidth * 12) * colNum
Text1.Top = Grid1.Top + (Grid1.RowHeight(0) + Grid1.GridLineWidth * 12) * rowNum
Text1.SetFocus
End Sub
透過「Grid 顯示 + TextBox 編輯 + 捲軸同步」的經典組合,我們在 VB 5.0 中成功打造出一個具備試算表操作感的數據輸入介面。此架構概念亦可移植至 VB6、VBA 或 .NET (DataGridView + EditingControl) 等新平台。掌握核心邏輯後,開發者可依專案需求擴充更豐富的試算表功能。