|
请各位老师帮忙修改一下代码功能,设计一个窗体能录入、修改、查询所有人员信息(在三个工作表中都应用),多谢。
- Private Sub CommandButton1_Click()
- Dim wsrg As Range
- Dim hang%
- On Error GoTo 判断
- Set wsrg = ActiveSheet.Range("B1") '查询数据源"数据表"工作表名称.B1人员姓名标题所在单元格
- hang = WorksheetFunction.Match(ListBox1.Value, wsrg.Parent.Range("B:B"), 0) 'B:B是指姓名所在的列
- ActiveWindow.ScrollRow = hang '选中的当前人员所在行滚动至工作表最顶端
- Rows(hang).Select '选中当前人员所在的行
- Unload Me '关闭当前的窗体
- Exit Sub
- 判断:
- MsgBox "没有选择查询人员!"
- End Sub
- Private Sub ListBox1_Click()
- Dim wsrg As Range
- Dim hang%
- Set wsrg = ActiveSheet.Range("B1") '查询数据源"数据表"工作表名称,B1人员姓名标题所在单元格
- 姓名.Caption = ListBox1.Value '姓名.Caption中的"姓名"是文本框的名称属性,下同理
- hang = WorksheetFunction.Match(姓名.Caption, wsrg.Parent.Range("B:B"), 0) 'B:B是指姓名所在的列
- 科室.Caption = ActiveSheet.Cells(hang, "D").Value '其中"数据表"是查询数据源工作表名,D是科室文本框对应数据的列位置,下同理
- 性别.Caption = ActiveSheet.Cells(hang, "Q").Value
- 受聘专业.Caption = ActiveSheet.Cells(hang, "J").Value
- 职务.Caption = ActiveSheet.Cells(hang, "O").Value
- 是否干部.Caption = ActiveSheet.Cells(hang, "S").Value
- 最高级别.Caption = ActiveSheet.Cells(hang, "F").Value
- 技术职称.Caption = ActiveSheet.Cells(hang, "G").Value
- 资格证书.Caption = ActiveSheet.Cells(hang, "T").Value
- 技术等级.Caption = ActiveSheet.Cells(hang, "W").Value
- 身份.Caption = ActiveSheet.Cells(hang, "E").Value
- 年龄.Caption = ActiveSheet.Cells(hang, "M").Value
- 年龄.Caption = ActiveSheet.Cells(hang, "M").Value
- 出生日期.Caption = Format(ActiveSheet.Cells(hang, "L").Value, "yyyy-mm-dd") '出生年月所在单元格及窗体中日期显示的格式
- 工作时间.Caption = Format(ActiveSheet.Cells(hang, "N").Value, "yyyy-mm-dd")
- With wsrg.Parent
- End With
- End Sub
- Private Sub TextBox1_Change()
- Dim lastrow%
- Dim myrg As Range
- TextBox1.Text = UCase(TextBox1.Text)
- ListBox1.Clear
- If TextBox1.Text = "" Then
- Label3 = 0
- Exit Sub
- End If
- lastrow = ActiveSheet.Range("B65536").End(xlUp).Row '其中"数据表"查询数据源工作表名称,B65536中的B表示姓名所在的列
- For Each myrg In ActiveSheet.Range("C2:C" & CStr(lastrow)) '其中"数据表"查询数据源工作表名称,"C2:C"拼音简码所在列
- If Right(TextBox1.Text, 1) = "0" Then
- If myrg = Left(TextBox1.Text, Len(TextBox1.Text)) Then ListBox1.AddItem ActiveSheet.Cells(myrg.Row, "B").Value '"B"指姓名所在列
- Else
- If myrg Like "*" & TextBox1.Text & "*" Then ListBox1.AddItem ActiveSheet.Cells(myrg.Row, "B").Value '"B"指姓名所在列
- End If
- Next
- Label3.Caption = ListBox1.ListCount
- End Sub
- Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
- If KeyCode = vbKeyDown Then
- If ListBox1.ListCount > 0 Then
- ListBox1.ListIndex = 0
- ListBox1.SetFocus
- End If
- End If
- End Sub
复制代码
|
|