使用EXCEL VBA创建任务跟踪表格,sheet1表中包含字段有序号、查证日期、提报方、提报方客户名称、提报方式、数量、窜货方、窜货方客户名称、是否结案、备注。每行末尾需添加botton按钮,点击后单独弹出窗体显示本行所有对应字段的信息。
时间: 2023-10-20 14:25:39 浏览: 138
以下是一个简单的示例代码,可以创建一个任务跟踪表格并添加按钮,点击按钮可以弹出窗体显示该行的信息:
```
Sub CreateTaskTracker()
'创建工作簿和工作表
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
ws.Name = "任务跟踪表"
'添加表头
ws.Range("A1").Value = "序号"
ws.Range("B1").Value = "查证日期"
ws.Range("C1").Value = "提报方"
ws.Range("D1").Value = "提报方客户名称"
ws.Range("E1").Value = "提报方式"
ws.Range("F1").Value = "数量"
ws.Range("G1").Value = "窜货方"
ws.Range("H1").Value = "窜货方客户名称"
ws.Range("I1").Value = "是否结案"
ws.Range("J1").Value = "备注"
'添加按钮
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim i As Long
For i = 2 To lastRow
Dim btn As Button
Set btn = ws.Buttons.Add(ws.Cells(i, 11).Left, ws.Cells(i, 11).Top, 50, 20)
btn.OnAction = "ShowTaskInfo"
btn.Caption = "详情"
btn.Name = "btn_" & i
Next i
End Sub
Sub ShowTaskInfo()
'获取按钮所在的行
Dim btn As Button
Set btn = ActiveSheet.Buttons(Application.Caller)
Dim row As Long
row = Mid(btn.Name, 5)
'弹出窗体显示信息
Dim info As String
info = "序号:" & Cells(row, 1).Value & vbCrLf _
& "查证日期:" & Cells(row, 2).Value & vbCrLf _
& "提报方:" & Cells(row, 3).Value & vbCrLf _
& "提报方客户名称:" & Cells(row, 4).Value & vbCrLf _
& "提报方式:" & Cells(row, 5).Value & vbCrLf _
& "数量:" & Cells(row, 6).Value & vbCrLf _
& "窜货方:" & Cells(row, 7).Value & vbCrLf _
& "窜货方客户名称:" & Cells(row, 8).Value & vbCrLf _
& "是否结案:" & Cells(row, 9).Value & vbCrLf _
& "备注:" & Cells(row, 10).Value
MsgBox info, vbInformation, "任务信息"
End Sub
```
你可以将以上代码复制到VBA编辑器中,然后运行CreateTaskTracker子程序来创建任务跟踪表格。每行末尾将会添加一个“详情”按钮,点击该按钮可以弹出窗体显示该行的信息。在ShowTaskInfo子程序中,你可以根据需要调整信息的显示格式和内容。
阅读全文