毕业设计方案(大学信息技术课毕业设计，VBA方案，单位职工宿舍管理系统)
职工宿舍入住数据管理系统
<<大学信息技术课程毕业设计VBA设计数据管理系统>>
1,系统主页
2,数据表页
3,数据查询页面
4,数据统计表页面
5,自定义设置页面
6,数据录入界面设计及VBA代码
Option Explicit
Private Sub CommandButton1_Click()
Call 新建
End Sub
Sub 新建()
t1 = ""
t2 = ""
t3 = ""
t4 = ""
t5 = ""
t6 = ""
t7 = ""
t8 = ""
t9 = ""
t10 = ""
t11 = ""
t12 = ""
t13 = ""
t14 = ""
t1.SetFocus
End Sub
Private Sub CommandButton2_Click()
'保存数据
Dim i As Integer
Dim r As Long
Dim rr As Long
Dim m As Long, n As Long
'不能为空的项
If t1.Text = "" Then MsgBox "楼号,不能为空,请认真填写!!!", vbInformation, "系统提示": Exit Sub
If t2.Text = "" Then MsgBox "称号,不能为空,请认真填写!!!", vbInformation, "系统提示": Exit Sub
If t3.Text = "" Then MsgBox "宿舍号,不能为空,请认真填写!!!", vbInformation, "系统提示": Exit Sub
If t5.Text = "" Then MsgBox "职工姓名,不能为空,请认真填写!!!", vbInformation, "系统提示": Exit Sub
If t7.Text = "" Then MsgBox "工号,不能为空,请认真填写!!!", vbInformation, "系统提示": Exit Sub
If t8.Text = "" Then MsgBox "身份证号,不能为空,请认真填写!!!", vbInformation, "系统提示": Exit Sub
With Sheets("数据表")
r = .Cells(.Rows.Count, "I").End(3).Row 1
If r < 4 Then r = 4
For i = 4 To r
'判断员工的编号不重要
If t7 = .Range("E" & i).Value Then MsgBox "请编号已经存在,此文件可能已经保存过,请确认之后,重新录入文档的编号信息!!!", vbInformation, "系统提示": Exit Sub
Next
If MsgBox("点击[ 是 ]数据将保存至[ 数据表 ]表,点击[ 否 ]撤消此次操作,并且数据不会保存至[ 数据表 ]表,请您选择!", vbInformation vbYesNo, "提示") = vbYes Then
'数据写入项目表
Sheets("数据表").Cells(r, 3).Value = t1.Value
Sheets("数据表").Cells(r, 4).Value = t2.Value
Sheets("数据表").Cells(r, 5).Value = t3.Value
Sheets("数据表").Cells(r, 6).Value = t4.Value
Sheets("数据表").Cells(r, 7).Value = t5.Value
Sheets("数据表").Cells(r, 8).Value = t6.Value
Sheets("数据表").Cells(r, 9).Value = t7.Value
Sheets("数据表").Cells(r, 10).Value = t8.Value
Sheets("数据表").Cells(r, 11).Value = t9.Value
Sheets("数据表").Cells(r, 12).Value = t10.Value
Sheets("数据表").Cells(r, 13).Value = t11.Value
Sheets("数据表").Cells(r, 14).Value = t12.Value
Sheets("数据表").Cells(r, 15).Value = t13.Value
Sheets("数据表").Cells(r, 16).Value = t14.Value
Call 新建
ThisWorkbook.Save
MsgBox "数据录入成功", vbInformation, "录入提示"
Else
Exit Sub
End If
End With
End Sub
Sub 初始化下拉列表加载()
Dim i As Integer, r As Integer
With UserForm1
'先清空一下
.t1.Clear
.t2.Clear
.t3.Clear
.t6.Clear
.t12.Clear
For i = 4 To Sheets("自定义").Range("c300").End(xlUp).Row '类别
.t1.AddItem Sheets("自定义").Cells(i, "C")
Next
For i = 4 To Sheets("自定义").Range("D300").End(xlUp).Row '类别
.t2.AddItem Sheets("自定义").Cells(i, "D")
Next
For i = 4 To Sheets("自定义").Range("E300").End(xlUp).Row '类别
.t3.AddItem Sheets("自定义").Cells(i, "E")
Next
For i = 4 To Sheets("自定义").Range("F300").End(xlUp).Row '类别
.t6.AddItem Sheets("自定义").Cells(i, "F")
Next
For i = 4 To Sheets("自定义").Range("G300").End(xlUp).Row '类别
.t12.AddItem Sheets("自定义").Cells(i, "G")
Next
.t1.ListIndex = 0
.t2.ListIndex = 0
.t3.ListIndex = 0
' .t6.ListIndex = 0
' .t12.ListIndex = 0
End With
End Sub
Private Sub CommandButton3_Click()
Sheets("数据表").Activate
End Sub
Private Sub CommandButton4_Click()
Sheets("查询").Activate
End Sub
Private Sub CommandButton5_Click()
Sheets("统计").Activate
End Sub
Private Sub CommandButton6_Click()
Sheets("自定义").Activate
End Sub
Private Sub CommandButton7_Click()
Sheets("说明").Activate
End Sub
Private Sub CommandButton8_Click()
Sheets("首页").Activate
End Sub
Private Sub UserForm_Activate()
Call 初始化下拉列表加载
End Sub
7,数据编辑界面设计及VBA代码
Option Explicit
Private Sub CommandButton1_Click()
'删除
Dim i As Integer
Dim r As Integer
Dim NO As String
If MsgBox("点击 是 将删除数据 点击 否 不会删除数据", vbInformation vbYesNo, "提示") = vbNo Then Exit Sub
NO = t3.Text
With Sheets("数据表")
r = .Cells(.Rows.Count, "E").End(3).Row
If r <= 3 Then Exit Sub
For i = 4 To r
If .Cells(i, "E").Value = NO Then .Rows(i).Delete
Next
End With
End Sub
Private Sub CommandButton2_Click()
'编辑
Dim i As Integer
Dim r As Integer
Dim NO As String
If MsgBox("点击 是 将编辑数据 点击 否 不会编辑数据", vbInformation vbYesNo, "提示") = vbNo Then Exit Sub
NO = t7.Text '编辑的标准
With Sheets("数据表")
r = .Cells(.Rows.Count, "E").End(3).Row
If r <= 3 Then Exit Sub
For i = 4 To r
If .Cells(i, "I").Value = NO Then
Sheets("数据表").Cells(i, 3).Value = t1.Value
Sheets("数据表").Cells(i, 4).Value = t2.Value
Sheets("数据表").Cells(i, 5).Value = t3.Value
Sheets("数据表").Cells(i, 6).Value = t4.Value
Sheets("数据表").Cells(i, 7).Value = t5.Value
Sheets("数据表").Cells(i, 8).Value = t6.Value
Sheets("数据表").Cells(i, 9).Value = t7.Value
Sheets("数据表").Cells(i, 10).Value = t8.Value
Sheets("数据表").Cells(i, 11).Value = t9.Value
Sheets("数据表").Cells(i, 12).Value = t10.Value
Sheets("数据表").Cells(i, 13).Value = t11.Value
Sheets("数据表").Cells(i, 14).Value = t12.Value
Sheets("数据表").Cells(i, 15).Value = t13.Value
Sheets("数据表").Cells(i, 16).Value = t14.Value
t1 = ""
t2 = ""
t3 = ""
t4 = ""
t5 = ""
t6 = ""
t7 = ""
t8 = ""
t9 = ""
t10 = ""
t11 = ""
t12 = ""
t13 = ""
t14 = ""
t1.SetFocus
End If
Next
End With
End Sub
Private Sub CommandButton3_Click()
Sheets("数据表").Activate
End Sub
Private Sub CommandButton4_Click()
Sheets("查询").Activate
End Sub
Private Sub CommandButton5_Click()
Sheets("统计").Activate
End Sub
Private Sub CommandButton6_Click()
Sheets("自定义").Activate
End Sub
Private Sub CommandButton7_Click()
Sheets("说明").Activate
End Sub
Private Sub CommandButton8_Click()
Sheets("首页").Activate
End Sub
Private Sub UserForm_Initialize()
Call 初始化下拉列表加载
End Sub
Sub 初始化下拉列表加载()
Dim i As Integer, r As Integer
With UserForm2
'先清空一下
.t1.Clear
.t2.Clear
.t3.Clear
.t6.Clear
.t12.Clear
For i = 4 To Sheets("自定义").Range("c300").End(xlUp).Row '类别
.t1.AddItem Sheets("自定义").Cells(i, "C")
Next
For i = 4 To Sheets("自定义").Range("D300").End(xlUp).Row '类别
.t2.AddItem Sheets("自定义").Cells(i, "D")
Next
For i = 4 To Sheets("自定义").Range("E300").End(xlUp).Row '类别
.t3.AddItem Sheets("自定义").Cells(i, "E")
Next
For i = 4 To Sheets("自定义").Range("F300").End(xlUp).Row '类别
.t6.AddItem Sheets("自定义").Cells(i, "F")
Next
For i = 4 To Sheets("自定义").Range("G300").End(xlUp).Row '类别
.t12.AddItem Sheets("自定义").Cells(i, "G")
Next
.t1.ListIndex = 0
.t2.ListIndex = 0
.t3.ListIndex = 0
End With
End Sub
8,系统功能框架设计
自定义:下拉列表设置
统计:统计部门人数
数据编辑窗口:删除,编辑,页面跳转
数据编辑窗口:删除,编辑,页面跳转(点击启用编辑的checkbox)
数据查询表:4个条件查询数据
数据录入窗口:新建,数据保存,页面跳转
数据表:用来保存职工入住信息
职工宿舍入住数据管理系统
