1、基本界面如下图：ListBox用于数据显示，Label显示标签，TextBox输入文本CommandButton按钮用于控制数据的更新、删除、添加。
2、定义窗体初始化：listbox显示数据Private Sub UserForm_Initialize()Worksheets("Sheet1").Selectc = Worksheets("Sheet1").Range("a1").End(xlToRight).Columnr = Worksheets("Sheet1").Range("A65536").End(xlUp).RowListBox1.ColumnCount = cListBox1.RowSource = Worksheets("Sheet1").Range("A1:" & Chr(64 + c) & r & "").AddressEnd Sub
3、定义查询按钮：Private Sub CommandButton1_Click()If TextBox1.Text = "" Then MsgBox "ÇëÊäÈëÐèÒª²éѯ»áÔ±ºÅ" Exit Sub End IfWith Worksheets("Sheet1") rs = r For i = 2 To rs If .Cells(i, 1) = TextBox1.Text Then TextBox1.Text = .Cells(i, 1) TextBox2.Text = .Cells(i, 2) TextBox3.Text = .Cells(i, 3) rl = i Exit For End If NextEnd WithEnd Sub功能为查询匹配项并显示在相应的TextBox中
4、定义添加按钮：Private Sub CommandButton2_Click()Dim ncs As Longncs = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1With Worksheets("Sheet1") .Cells(ncs, 1) = TextBox1.Text .Cells(ncs, 2) = TextBox2.Text .Cells(ncs, 3) = TextBox3.TextEnd WithActiveWorkbook.SaveCall UserForm_InitializeTextBox1.Text = ""TextBox2.Text = ""TextBox3.Text = ""End Sub功能为在最后一行添加数据，并清空TextBox
5、依次添加组件的方法：以下是整个程序的代码：Dim c As 雉搽妤粲LongDim r As LongDim rl As LongPrivate Sub Comm锾攒揉敫andButton1_Click()If TextBox1.Text = "" Then MsgBox "请输入需要查询会员号" Exit Sub End IfWith Worksheets("Sheet1") rs = r For i = 2 To rs If .Cells(i, 1) = TextBox1.Text Then TextBox1.Text = .Cells(i, 1) TextBox2.Text = .Cells(i, 2) TextBox3.Text = .Cells(i, 3) rl = i Exit For End If NextEnd WithEnd SubPrivate Sub CommandButton2_Click()Dim ncs As Longncs = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1With Worksheets("Sheet1") .Cells(ncs, 1) = TextBox1.Text .Cells(ncs, 2) = TextBox2.Text .Cells(ncs, 3) = TextBox3.TextEnd WithActiveWorkbook.SaveCall UserForm_InitializeTextBox1.Text = ""TextBox2.Text = ""TextBox3.Text = ""End SubPrivate Sub CommandButton3_Click()c = ListBox1.ListIndex + 1If c = 0 Then MsgBox "请选择一条数据" Exit SubEnd IfRows(c).DeleteEnd SubPrivate Sub CommandButton4_Click()If TextBox1.Text = "" And TextBox2.Text = "" And TextBox3.Text = "" And r <> 0 Then MsgBox "请先进行相应的数据查询"Else r = rl With Worksheets("Sheet1") .Cells(r, 1) = TextBox1.Text .Cells(r, 2) = TextBox2.Text .Cells(r, 3) = TextBox3.Text ActiveWorkbook.Save UserForm_Initialize End WithEnd IfEnd SubPrivate Sub CommandButton5_Click()Unload MeEnd SubPrivate Sub CommandButton6_Click()c = Worksheets("Sheet1").Range("a1").End(xlToRight).Column + 1ad = InputBox("输入增加项名称", "增加项", "")If ad <> "" ThenWorksheets("Sheet1").Cells(1, c) = adEnd IfEnd SubPrivate Sub ListBox1_Click()End SubPrivate Sub UserForm_Click()End SubPrivate Sub UserForm_Initialize()Worksheets("Sheet1").Selectc = Worksheets("Sheet1").Range("a1").End(xlToRight).Columnr = Worksheets("Sheet1").Range("A65536").End(xlUp).RowListBox1.ColumnCount = cListBox1.RowSource = Worksheets("Sheet1").Range("A1:" & Chr(64 + c) & r & "").AddressEnd Sub
6、窗体的控件由以下工具箱添加。并在this work book中添加如下代码，引入菜单栏工具。便于启动。
7、实例运行：
