Question

excel中使用VBA对话框

如何使用对话框，将输入的文本，替代下面程序中的“班级”，并用对话框返回“M2”单元格中的结果值
Sheets("ETINFO(正式版)").Select
ActiveWindow.SmallScroll Down:=-6
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="班级"
Selection.AutoFilter Field:=7, Criteria1:="姓名"
Cells.Select
Selection.Copy
Sheets("复制").Select
Range("A1").Select
ActiveSheet.Paste
Range("M2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-10],""HS4B4"")"

Answer 1

用VBA内置函数inputbox来输入文本，使您的“班级”成为变量。用msgbox来回返M2单元格中的结果值。
DIM bj AS String
bj=inputbox("请输入您的文本","请输入")
Sheets("ETINFO(正式版)").Select
ActiveWindow.SmallScroll Down:=-6
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=bj
Selection.AutoFilter Field:=7, Criteria1:="姓名"
Cells.Select
Selection.Copy
Sheets("复制").Select
Range("A1").Select
ActiveSheet.Paste
Range("M2").Select
msgbox range("m2").value
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-10],""HS4B4"")"
如果你的姓名也需要成为变量，再增加一个变量并用inputbox为变量赋值。
textbox只是文本框，不是对话框。

Answer 2

假如输入的内容在对话框的textbox1 输出内容在textbox2
Sheets("ETINFO(正式版)").Select
ActiveWindow.SmallScroll Down:=-6
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=textbox1.text '输入的文本
Selection.AutoFilter Field:=7, Criteria1:="姓名"
Cells.Select
Selection.Copy
Sheets("复制").Select
Range("A1").Select
ActiveSheet.Paste
Range("M2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-10],""HS4B4"")"
textbox2.text = range("M2").value '对话框中textbox2显示M2值