Excel VBA编辑会员信息管理系统
1、基本界面如下图：
ListBox用于数据显示，Label显示标签，TextBox输入文本
CommandButton按钮用于控制数据的更新、删除、添加。
2、定义窗体初始化：listbox显示数据
Private Sub UserForm_Initialize()
Worksheets("Sheet1").Select
c = Worksheets("Sheet1").Range("a1").End(xlToRight).Column
r = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
ListBox1.ColumnCount = c
ListBox1.RowSource = Worksheets("Sheet1").Range("A1:" & Chr(64 + c) & r & "").Address
End Sub
3、定义查询按钮：
Private Sub CommandButton1_Click()
If TextBox1.Text = "" Then
MsgBox "ÇëÊäÈëÐèÒª²éѯ»áÔ±ºÅ"
Exit Sub
End If
With 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
Next
End With
End Sub
功能为查询匹配项并显示在相应的TextBox中
4、定义添加按钮：
Private Sub CommandButton2_Click()
Dim ncs As Long
ncs = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1
With Worksheets("Sheet1")
.Cells(ncs, 1) = TextBox1.Text
.Cells(ncs, 2) = TextBox2.Text
.Cells(ncs, 3) = TextBox3.Text
End With
ActiveWorkbook.Save
Call UserForm_Initialize
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub
功能为在最后一行添加数据，并清空TextBox
5、依次添加组件的方法：
以下是整个程序的代码：
Dim c As Long
Dim r As Long
Dim rl As Long
Private Sub CommandButton1_Click()
If TextBox1.Text = "" Then
MsgBox "请输入需要查询会员号"
Exit Sub
End If
With 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
Next
End With
End Sub
Private Sub CommandButton2_Click()
Dim ncs As Long
ncs = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1
With Worksheets("Sheet1")
.Cells(ncs, 1) = TextBox1.Text
.Cells(ncs, 2) = TextBox2.Text
.Cells(ncs, 3) = TextBox3.Text
End With
ActiveWorkbook.Save
Call UserForm_Initialize
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub
Private Sub CommandButton3_Click()
c = ListBox1.ListIndex + 1
If c = 0 Then
MsgBox "请选择一条数据"
Exit Sub
End If
Rows(c).Delete
End Sub
Private 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 With
End If
End Sub
Private Sub CommandButton5_Click()
Unload Me
End Sub
Private Sub CommandButton6_Click()
c = Worksheets("Sheet1").Range("a1").End(xlToRight).Column + 1
ad = InputBox("输入增加项名称", "增加项", "")
If ad <> "" Then
Worksheets("Sheet1").Cells(1, c) = ad
End If
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
Worksheets("Sheet1").Select
c = Worksheets("Sheet1").Range("a1").End(xlToRight).Column
r = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
ListBox1.ColumnCount = c
ListBox1.RowSource = Worksheets("Sheet1").Range("A1:" & Chr(64 + c) & r & "").Address
End Sub
6、窗体的控件由以下工具箱添加。
并在this work book中添加如下代码，引入菜单栏工具。便于启动。
7、实例运行：
