你知道吗?EXCEL结合VBA可以实现界面清晰简单的信息管理系统,它不一定要是软件,也不需要考虑安装,只要你有Office办公软件,它就能帮你实现超级实用的信息管理。
界面如下:
表格1是功能界面
表格二是数据库,内容可以无限添加
实现代码如下:
Private Sub CommandButton1_Click()
'处理未指定料号的查询
If Len(TextBox1.Value) = 0 Then
MsgBox ("请先指定需要查询的料号!")
Exit Sub
End If
'以料号作为查询条件,获取指定的料号信息所在的行
Dim row As Long
row = FindNameRow()
'处理查询不存在的情形
If row < 1 Then
MsgBox ("无法查询到料号为" & """" & TextBox1.Value & """" & "的信息,请确认后再试!")
Exit Sub
End If
'item指定要填充的字段名,col代表查找该字段所在的列(数字,第几列)
Dim item As String, col As Integer
col = FindItemColumn(Worksheets("sheet1").Range("B21").Value)
Worksheets("sheet1").Range("B22").Value = ThisWorkbook.Sheets(2).Cells(row, col).Value
col = FindItemColumn(Worksheets("sheet1").Range("C21").Value)
Worksheets("sheet1").Range("C22").Value = ThisWorkbook.Sheets(2).Cells(row, col).Value
col = FindItemColumn(Worksheets("sheet1").Range("D21").Value)
Worksheets("sheet1").Range("D22").Value = ThisWorkbook.Sheets(2).Cells(row, col).Value
End Sub
'查找指定姓名所在的行
Function FindNameRow()
'已使用行数
Dim num As Integer
num = ThisWorkbook.Sheets(2).Range("A1").CurrentRegion.Rows.Count
'依次测试每个单元格的值,如果是指定值,则返回指定值所在的行
Dim rng As Range, row As Long
For Each rng In ThisWorkbook.Sheets(2).Range("A2:A" & num)
If rng.Value = TextBox1.Value Then
row = rng.row
Exit For
End If
Next
FindNameRow = row
End Function
'查找各字段所在的列(数值,第几列)
Function FindItemColumn(item)
'已使用区域列数
Dim num As Integer
num = ThisWorkbook.Sheets(2).Range("A1").CurrentRegion.Columns.Count
'依次测试包含字段名的单元格区域,如果是指定字段名,则返回所在的列(数值,第几列)
Dim rng As Range, col As Integer
For Each rng In ThisWorkbook.Sheets(2).Range("A1", ThisWorkbook.Sheets(2).Cells(1, num))
If rng.Value = item Then
col = rng.Column
Exit For
End If
Next
FindItemColumn = col
End Function
Private Sub CommandButton2_Click()
TextBox1.Text = ""
Range("B22:K22").ClearContents
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then CommandButton1_Click
End Sub