在一些应用场景中,我们可能会需要根据工作表中的数据来创建用户窗体中的控件。例如下图1所示,在工作表第3行中有一行标题数据,想要根据标题数量在用户窗体中创建标签和相应的文本框。
图1
按Alt+F11组合键,打开VBE,单击菜单“插入——用户窗体”,在该用户窗体中放置一个框架控件,如下图2所示。
图2
在该用户窗体中单击右键,选择“查看代码”命令,输入下面的代码:
Private Sub UserForm_Initialize()
Dim rngData As Range
Dim rng As Range
Dim i As Integer
Dim iTop As Integer
Dim lbl1 As MSForms.Control
Dim txt1 As MSForms.Control
Set rngData = ActiveSheet.Range("A3").CurrentRegion
With Me.Frame1.Controls
iTop = 15
For i= 1 To rngData.Columns.Count
Set lbl1 = .Add("Forms.label.1")
With lbl1
.Top = iTop
.Left = 15
.Width = 90
.Caption = rngData.Cells(1, i).Value & ": "
End With
iTop = iTop + 25
Next i
iTop= 15
For i= 1 To rngData.Columns.Count
Set txt1 = .Add("Forms.textbox.1")
With txt1
.Top = iTop
.Left = 50
.Width = 85
.Tag = i
.ControlTipText = "输入:" & rngData.Cells(1, i).Value
End With
iTop = iTop + 25
Next i
End With
If i >10 Then
With Me.Frame1
.Caption = "数据输入"
.ScrollBars = fmScrollBarsVertical
.ScrollHeight = .InsideHeight * i / 10
End With
End If
End Sub
运行窗体后,效果如上图1所示。
你可以以此为基础,继续添加功能。例如,用户在文本框中输入内容后,自动输入到工作表中;清空文本框中的内容;等等。